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?
First, inspect the name given to the FK by the RDBMS, it has the same prefix and body but differs only in suffix hash. Second, select names of these constraints. Third, exec alter command that drops them.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With