PostgreSQL drop constraint with unknown name

Question

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?

Edeline

You would at least need dynamic SQL to do this. Object names (tablenames,columnnames,etc) can Never be specified as variables (or subqueries) without first constructing the query (in a string) and then executing that. Using sed/awk to generate the "DROP xxx" lines in a file, and piping that file through psql could be a workaround. It will still be hard to maintain some "atomicity" (But DDL's are always difficult in that respect) – Flaxman

@Flaxman okay, I thought maybe psql would have some functionality to do that. Otherwise, you can just put your comment as answer. – Edeline

I am not that handy with dynamic query building (I actually hate it) Others will probably fill it in. – Flaxman

Filling in the dynamic stuff ... – Songful

Answer 1

To dynamically drop & recreate a foreign key constraint, you could wrap it all in 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 own the table to use ALTER TABLE.
Else you can create a function with LANGUAGE plpgsql SECURITY DEFINER (using the same body) and

ALTER FUNCTION foo() OWNER TO postgres;

postgres being a superuser here - or the owner of the table.
But be sure to know what the manual has to say about security.

The manual also has more on dynamic commands.

Songful

Answer 2

You can use stored procedure also.

CREATE OR REPLACE PROCEDURE public.p_costraint()
 LANGUAGE plpgsql
AS $procedure$
DECLARE _constrint text;
begin
-- for dynamic change the constraint. 
    _constrint := (
      SELECT quote_ident(conname)
      FROM   pg_constraint
        WHERE  conrelid = 'test.contacts'::regclass
        AND    confrelid = 'test.customers'::regclass
      LIMIT 1 -- there could be multiple fk constraints. Deal with it ...
      );
    _constrint := _constrint || 'test';
    EXECUTE '
      ALTER TABLE test.contacts
      ADD CONSTRAINT ' || _constrint || ' FOREIGN KEY (customer_id)
      REFERENCES test.customers (customer_id)';
    RAISE NOTICE 'hello, world!';
end
$procedure$;

In here. constraint name is used as a text variable.
You can just call it: call public.p_costraint();
It will return :

NOTICE:  hello, world!
CALL

Orren