从一个表中提取列值并修改后插入到另一个表中

Question

我创建了一个 PL/pgSQL 函数，它接受两个列名、一个“关系”和两个表名。它在一个表中查找不同的行并将它们插入到临时表中，删除任何具有空值的行，并将一列的所有值设置为关系。我有使用此功能的过程的第一部分。

create or replace function alt_edger(s text, v text, relation text, tbl text, tbl_src text)
    returns void
    language plpgsql as
$func$
begin
    raise notice 's: %, v: %, tbl: %, tbl_src: %', s,v,tbl,tbl_src;
    execute ('insert into '||tbl||' ("source", "target") select distinct "'||s||'","'||v||'" from '||tbl_src||'');
    execute ('DELETE FROM '||tbl||' WHERE "source" IS null or "target" is null'); 
end
$func$;

它的执行方式如下：

-- create a temporary table and execute the function twice
drop table if exists temp_stack;
create temporary table temp_stack("label" text, "source" text, "target" text, "attr" text, "graph" text);
 
select alt_edger('x_x', 'y_y', ':associated_with', 'temp_stack','pg_check_table' );
select alt_edger('Document Number', 'x_x', ':documents', 'temp_stack','pg_check_table' );

select * from temp_stack;

请注意，我还没有使用 relation。 INSERT 还应分配 relation，但我不知道如何实现这一点，例如：

label	source	target	attr	graph
:linked_with	638000	ARAS
:linked_with	202000	JASE
：关联_与	638010	JASE
：关联_与	638000	JASE
：关联_与	202100	JASE
:documents	A	638010
:documents	A	202000
:	A	202100
documents	:documents B	638000
:	documents A	638000
:documents	B	124004
:documents	B	202100

我的挑战是：

• 如何在 INSERT< 中集成 relation /代码>?当我尝试使用 VALUES 和逗号分隔时，我收到“选择附近错误”。
• 如何允许relation中以“:”开头的字符串？我在这里预计，包含冒号在过去给我带来了挑战。

我该怎么做？或者有更好的方法吗？

玩具数据模型：

drop table if exists pg_check_table;
create temporary table pg_check_table("Document Number" text, x_x int, y_y text);
insert into pg_check_table values ('A',202000,'JASE'),
('A',202100,'JASE'),
('A',638010,'JASE'),
('A',Null,'JASE'),
('A',Null,'JASE'),
('A',202100,'JASE'),
('A',638000,'JASE'),
('A',202100,'JASE'),
('B',638000,'JASE'),
('B',202100,null),
('B',638000,'JASE'),
('B',null,'ARAS'),
('B',638000,'ARAS'),
('B',null,'ARAS'),
('B',638000,null),
('B',124004,null);
alter table pg_check_table add row_num serial;
select * from pg_check_table;

Answer 1

-- DROP FUNCTION alt_edger(_s text, _v text, _relation text, _tbl text, _tbl_src text)
CREATE OR REPLACE FUNCTION alt_edger(_s text, _v text, _relation text, _tbl text, _tbl_src text, OUT row_count int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql text := format(
       'INSERT INTO pg_temp.%3$I (label, source, target)
        SELECT DISTINCT $1, %1$I, %2$I FROM pg_temp.%4$I
        WHERE (%1$I, %2$I) IS NOT NULL'
      , _s, _v, _tbl, _tbl_src);
BEGIN
   -- RAISE NOTICE '%', _sql;  -- debug
   EXECUTE _sql USING _relation;
   GET DIAGNOSTICS row_count = ROW_COUNT;  -- return number of inserted rows
END
$func$;

db<>fiddle 此处

最重要的是，使用<一href="https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT" rel="nofollow noreferrer">format()安全地连接动态 SQL 命令。并使用格式说明符 %I 作为标识符。这样，SQL 注入就不可能实现，并且标识符会被正确地用双引号括起来 - 保留非标准名称，例如 Document Number。这就是你原来的失败的地方。

我们也可以将 _relation 连接为要插入到 label 中的字符串。但将值传递给EXECUTE的更好方法是使用USING子句。传递给 EXECUTE 的 SQL 字符串内的 $1 是第一个 USING 参数的占位符。不要与在函数体外部EXECUTE上下文中引用函数参数的$1相混淆！ （您可以传递任何字符串，前导冒号（：）并不重要，正确完成后不会解释该字符串。）
请参阅：

• format 中整数变量的格式说明符( ) 执行？
• 表名称作为 PostgreSQL 函数参数

我替换了 DELETE 在您的原始文件中，在 INSERT 的 SELECT 中添加 WHERE 子句。不要首先插入行，而是稍后再次删除它们。

(%1$I, %2$I) IS NOT NULL 仅当两个值均为 NOT NULL 时才符合条件。
关于这一点：

• 检查 Postgres 复合字段是否为null/empty

不要使用前缀“pg_”作为表名。这就是 Postgres 用于系统表的内容。别搞乱那些。

我使用 pg_temp 对已知临时表进行架构限定。这通常是可选的，因为默认情况下临时架构位于 search_path 的第一位。但这可以被更改（恶意），然后表名称将解析为 search_path 中任何现有的同名常规表。所以安全总比后悔好。请参阅：

• 如何search_path 影响标识符解析和“当前架构”

我使函数返回插入的行数。这是完全可选的！
由于我使用 OUT 参数执行此操作，因此我可以跳过 RETURNS 子句。请参阅：

• 我可以让 plpgsql 函数在不使用变量的情况下返回整数吗？