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?
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.