PostgreSQL quotation mark with unknown name

Question

I have an SQL script that needs to remove several constraints and restore them at the end, but the constraint names are automatically generated and will be different each time the script is run.

I know how to get the name of a constraint from table names, but it is not possible to use this information in a drop statement.

select conname from pg_constraint where conrelid = (select oid from pg_class where relname='table name') and confrelid = (select oid from pg_class where relname='reference table'); 

alter table something drop constraint (some subquery) is a syntax error.

Ideally, I would like to get the name of the constraint and store it in a variable, but Postgres doesn't seem to support this, and I can't get it to work with psql \set .

Is it possible?

Answer 1

To dynamically drop and recreate a foreign key constraint, you can transfer all this to a function or use the DO command:

 DO $body$ DECLARE _con text := ( SELECT quote_ident(conname) FROM pg_constraint WHERE conrelid = 'myschema.mytable'::regclass AND confrelid = 'myschema.myreftable'::regclass LIMIT 1 -- there could be multiple fk constraints. Deal with it ... ); BEGIN EXECUTE ' ALTER TABLE wuchtel12.bet DROP CONSTRAINT ' || _con; -- do stuff here EXECUTE ' ALTER TABLE myschema.mytable ADD CONSTRAINT ' || _con || ' FOREIGN KEY (col) REFERENCES myschema.myreftable (col)'; END $body$ 

You must use a table to use ALTER TABLE .
Otherwise, you can create a function using LANGUAGE plpgsql SECURITY DEFINER (using the same theme) and

 ALTER FUNCTION foo() OWNER TO postgres; 

postgres is the superuser here - or the owner of the table.
But be sure to find out what management has to say about security .

The manual also specifies more dynamic commands.