PostgreSQL drop constraint with unknown name

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

I know how to get the constraint name from the table names, but it doesn't seem possible to use this information in the 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 constraint name and store it in a variable, but it doesn't seem that Postgres supports that and I can't make it work with psql \set.

Is this even possible?

Solution

To dynamically drop & recreate a foreign key constraint, you could wrap it all in a FUNCTION, PROCEDURE (Postgres 11+), or a DO command:

DO
$do$
DECLARE
   /* There could be multiple FK constraints one table to another (even if uncomon).
    * This kind of assignment raises an exception if more than one rows are found:
    * "ERROR:  more than one row returned by a subquery used as an expression"
    * (As opposed to SELECT INTO ...)
    * If that's not what you want, resolve it.
    */
   _con text := (
      SELECT quote_ident(conname)
      FROM   pg_constraint
      WHERE  conrelid = 'myschema.mytable'::regclass      -- source table
      AND    confrelid = 'myschema.myreftable'::regclass  -- FK target table
      );
BEGIN
   IF _con IS NULL THEN
      RAISE EXCEPTION 'FK constraint not found!';  -- flesh out msg ...
   END IF;

   EXECUTE
     'ALTER TABLE myschema.mytable DROP CONSTRAINT ' || _con;

   -- do stuff here

   EXECUTE
     'ALTER TABLE myschema.mytable
      ADD CONSTRAINT ' || _con || ' FOREIGN KEY (col)
      REFERENCES myschema.myreftable (col)';
END
$do$;

The executing role must own the table to use ALTER TABLE (or be a superuser).
Else you can create a SECURITY DEFINER function with using the same body, and:

ALTER FUNCTION foo() OWNER TO table_owner;

table_owner being the owner of the table (or a superuser). But read what the manual has to say about security.

The manual on dynamic commands.