未知名称的PostgreSQL删除约束 [英] PostgreSQL drop constraint with unknown name

问题描述

我有一个SQL脚本，需要删除几个约束并在最后恢复它们，但是约束名称是自动生成的，并且每次运行脚本时都会不同.

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.

我知道如何从表名中获取约束名称，但似乎无法在drop语句中使用此信息.

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)是语法错误.

理想情况下，我想获取约束名称并将其存储在变量中，但似乎Postgres不支持该名称，并且我无法使其与psql \set一起使用.

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.

这有可能吗?

推荐答案

要动态删除&重新创建外键约束，您可以将其全部包装在函数中，或使用 DO 命令:

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$

您必须拥有表才能使用ALTER TABLE.
另外，您可以使用LANGUAGE plpgsql SECURITY DEFINER(使用相同的正文)并使用LANGUAGE plpgsql SECURITY DEFINER 创建函数. /p>

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是此处的超级用户-或表的所有者.
但是一定要知道手册必须说的安全性.

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

该手册还提供了有关动态命令的更多信息.

这篇关于未知名称的PostgreSQL删除约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！