CODEWITHSUNDEEP
postgresqlschema
Trung Tran
Trung Tran

Reputation: 13771

Alter table across all postgresql schemas

If I have several schemas that contain the same table, is there a way for me to make an update to all the tables at once? For example, if I have 3 schemas that each have a user table with columns first_name, last_name, email and I want to add a column for phone_num for each user table in all 3 schemas, is there a way i can do it? I could not find a way in the postgresql docs...

Thanks in advance!

Upvotes: 2

Views: 1068

Answers (1)

shiv
shiv

Reputation: 2100

I think you want to alter table and not to update the table. If yes then below code will work for you,

-- Function: alter_table()

-- DROP FUNCTION alter_table();

CREATE OR REPLACE FUNCTION alter_table()
  RETURNS integer AS
$BODY$

DECLARE
  v_schema text;
BEGIN

FOR v_schema IN
    SELECT quote_ident(nspname)  
    FROM   pg_namespace n
    WHERE  nspname !~~ 'pg_%'
    AND    nspname <>  'information_schema'
LOOP
   EXECUTE 'SET LOCAL search_path = ' || v_schema;
   ALTER TABLE "user"  ADD COLUMN show_price boolean NOT NULL DEFAULT TRUE;
END LOOP;

return 1;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION alter_table()
  OWNER TO postgres;

Upvotes: 6

Related Questions