PostgreSQL usefull scrips

add_remove_column_to_all_tables.sql

--add
select  
  f_add_col(cast(tablename as text), 'created_at', 'timestamptz', 'now()') 
from 
  pg_catalog.pg_tables 
where 
  schemaname = 'public';

--drop
--select f_drop_col(cast(tablename as text), 'created_at' )  from pg_catalog.pg_tables where schemaname = 'public';



/* Good POSTGRESQL material : http://www.postgresqltutorial.com/ */

f_add_col.sql

-- function to add a column
CREATE OR REPLACE function f_add_col(_tbl regclass, _col  text, _type regtype, _default text)
  RETURNS bool AS
$func$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_attribute
              WHERE  attrelid = _tbl
              AND    attname = _col
              AND    NOT attisdropped) THEN
      RETURN FALSE;
   ELSE
      EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s default %s', _tbl, _col, _type, _default);
      RETURN TRUE;
   END IF;
END
$func$  LANGUAGE plpgsql;

f_drop_col.sql

CREATE OR REPLACE function f_drop_col(_tbl regclass, _col  text)
  RETURNS bool AS
$func$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_attribute
              WHERE  attrelid = _tbl
              AND    attname = _col
              AND    NOT attisdropped) THEN
       EXECUTE format('ALTER TABLE %s drop COLUMN %I ', _tbl, _col);
   RETURN true;
   ELSE
      
      RETURN false;
   END IF;
END
$func$  LANGUAGE plpgsql;