Postgres 原子存储过程插入到 . . .使用表中的一个参数和一组行进行 SELECT

Question

我正在尝试编写一个存储过程，让开发人员在没有指定组时将新用户身份分配给指定组(即将参数和 select 语句的输出插入到连接表中)，而无需手动编写每对外键作为值来执行此操作。我知道如何在 T-SQL/SQL Server 中做到这一点，但我正在使用一个预先存在/不熟悉的 Postgres 数据库。我强烈希望将我的存储过程保持为LANGUAGE SQL/BEGIN ATOMIC，这 + 在线示例被简化和/或使用常量使我难以理解。

提前道歉，这是我试图阐明为什么我不相信这个问题是重复的，基于我自己能够找到的搜索，但我可能矫枉过正了。

模式(从最容易识别的部分抽象出来;这些不是原始的表名，我无法更改任何名称;为了简单起见，我也省略了索引)就像：

create table IF NOT EXISTS user_identities (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL,
[more columns not relevant to this query)
)
create table IF NOT EXISTS user_groups (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL,
name TEXT NOT NULL
) 
create table IF NOT EXISTS group_identities (
user_id BIGINT REFERENCES user_identities(id) ON DELETE RESTRICT NOT NULL,
group_id BIGINT REFERENCES user_groups(id) ON DELETE RESTRICT NOT NULL
)

预期的开发行为：

1. 在单个批次中添加打算属于某个组的所有预定身份
2. 为新组添加标识信息(如果可以的话，需要很多说服力才能让相关人员为此使用嵌套存储过程)
3. 相应地使联接表保持最新(我被要求简化的内容)。

如果这是SQL Server，我会这样做(请省略错误处理时间，并搁置EXCEPT或NOT IN是目前最好的)

create OR alter proc add_identities_to_group 
@group_name varchar(50) NULL
as BEGIN 
declare @use_group_id int 
if @group_name is NULL 
set @use_group_id = (select Top 1 id from user_groups where id not in (select group_id from group_identities) order by id asc) 
ELSE set @use_group_id = (select id from user_groups where name = @group_name)
insert into group_identities (user_id, group_id) 
select @use_group_id, id from user_identities 
where id not in (select user_id from group_identities)
END 
GO 

显然，这在Postgres中是行不通的;我想坚持使用原子存储过程的部分原因是保持"中立"SQL，这既是为了更接近我的舒适区，也是因为我不知道数据库目前设置了哪些其他语言，但是我现有的教育在区分T-SQL特定的内容方面玩得有点快和松散。

我知道由于各种原因，这不会运行，因为我仍在尝试内化语法，但是我写的糟糕/概念性的草稿是：

create OR replace procedure add_identities_to_groups( 
group_name text default NULL ) language SQL 
BEGIN ATOMIC 
declare use_group_id integer
if group_name is NULL 
set use_group_id = (select Top 1 id from user_groups
where id not in (select user_id from group_identities)
order by id asc) 
ELSE set use_group_id = (select id from user_groups where name = group_name) ;
insert into group_identities (group_id, user_id)
select use_group_id, id from user_identities
where id not in (select user_id from group_identities)
END ; 
GO ; 

问题：

• 还没有找到有关如何使用单个变量和具有BEGIN ATOMIC的列的组合来执行此操作的答案，或者硬确认它不起作用(例如，原子存储过程可以不接受参数吗？我自己找不到答案)。(这就是为什么我能在这里和其他地方找到的现有答案没有为我澄清的部分原因。
• ~~不知道如何补偿Postgres根本没有将变量和参数与列名区分开来。(这就是为什么使用硬编码常量的示例没有帮助的原因，它们几乎构成了我从 StackOverflow 本身可以找到的所有内容。~~ 如果 Postgres 会在原子块内智能地处理这个问题，这不是问题，但这是我自己无法确认的事情之一。
• 无论如何，谷歌对"香草"SQL的结果都不可预测地饱和了SQL Server，而我对Postgres的不熟悉对我没有任何好处，但我个人不认识比我更有经验的人。

Answer 1

因为我不知道数据库当前设置了哪些其他语言

所有支持的Postgres版本始终包含PL/pgSQL。

如果你想使用过程元素，如变量或条件语句，如IF你需要PL/pgSQL。因此，您的过程必须使用language plpgsql定义 - 这消除了使用 ANSI 标准BEGIN ATOMIC语法的可能性。

根本不知道如何补偿 Postgres 根本没有将变量和参数与列名区分开来。

你没有。大多数人只是使用命名约定来做到这一点。在我的环境中，我们对参数使用p_，对"局部"变量使用l_。使用您喜欢的任何东西。

手册中的引用

默认情况下，如果 SQL 语句中的名称可以引用变量或表列，PL/pgSQL 将报告错误。您可以通过重命名变量或列，或者限定不明确的引用，或者告诉PL/pgSQL首选哪种解释来解决此类问题。
最简单的解决方案是重命名变量或列。常见的编码规则是对 PL/pgSQL 变量使用与列名不同的命名约定。例如，如果始终将函数变量命名为v_something而列名都不以 v_ 开头，则不会发生冲突。

如手册中所述，用PL/pgSQL(或任何其他非SQL语言)编写的过程的正文必须以字符串形式提供。这通常使用美元报价来完成，以使编写源更容易。

如手册中所述，如果要将单行查询的结果存储在变量中，请使用select ... into from ...。

如手册中所述，IF语句需要THEN

如手册中所述，Postgres(或标准SQL)中没有TOP子句。请改用limit或符合标准的fetch first 1 rows only。

为了避免变量名称和列名之间的冲突，大多数人对参数和变量使用某种前缀。这也有助于在代码中识别它们。

在 Postgres 中，使用 NOT EXISTS 而不是 NOT IN 通常更快。

在 Postgres 语句中以;结尾。GO也不是SQL Server中的SQL命令 - 它是SSMS支持的客户端命令。据我所知，没有与 Postgres 一起工作的 SQL 工具可以像 SSMS 一样支持GO"批处理终止符"。

因此，将 T-SQL 代码直接转换为 PL/pgSQL 可能如下所示：

create or replace procedure add_identities_to_groups(p_group_name text default NULL) 
language plpgsql 
as
$$ --<< start of PL/pgSQL code 
declare --<< start a block for all variables
l_use_group_id integer;
begin  --<< start the actual code
if p_group_name is NULL THEN --<< then required
select id 
into l_use_group_id
from user_groups ug
where not exists (select * from group_identities gi where gi.id = ug.user_id)
order by ug.id asc
limit 1;
ELSE 
select id 
into l_use_group_id 
from user_groups  
where name = p_group_name;
end if;

insert into group_identities (group_id, user_id)
select l_use_group_id, id 
from user_identities ui
where not exists (select * from group_identities gi where gi.user_id = ui.id);
END;
$$
;