postgresql - 动态SQL中,如何引用变量?

  显示原文与译文双语对照的内容
0 0

我试着为表upserts编写一个PostgreSQL函数,它可以用于任何表。 从具体的表类型的具体函数中获取起始点:


CREATE TABLE doodad(id BIGINT PRIMARY KEY, data JSON);
CREATE OR REPLACE FUNCTION upsert_doodad(d doodad) RETURNS VOID AS
 $BODY$
BEGIN
 LOOP
 UPDATE doodad
 SET id = (d).id, data = (d).data
 WHERE id = (d).id;
 IF found THEN
 RETURN;
 END IF;

 -- does not exist, or was just deleted.

 BEGIN
 INSERT INTO doodad SELECT d.*;
 RETURN;
 EXCEPTION when UNIQUE_VIOLATION THEN
 -- do nothing, and loop to try the update again
 END;

 END LOOP;
END;
 $BODY$
LANGUAGE plpgsql;

对于任何我提出的表的动态SQL版本,都在这里:


CREATE OR REPLACE FUNCTION upsert(target ANYELEMENT) RETURNS VOID AS
$$
DECLARE
 attr_name NAME;
 col TEXT;
 selectors TEXT[];
 setters TEXT[];
 update_stmt TEXT;
 insert_stmt TEXT;
BEGIN
 FOR attr_name IN SELECT a.attname
 FROM pg_index i
 JOIN pg_attribute a ON a.attrelid = i.indrelid 
 AND a.attnum = ANY(i.indkey)
 WHERE i.indrelid = format_type(pg_typeof(target), NULL)::regclass
 AND i.indisprimary
 LOOP
 selectors := array_append(selectors, format('%1$s = target.%1$s', attr_name));
 END LOOP;

 FOR col IN SELECT json_object_keys(row_to_json(target))
 LOOP
 setters := array_append(setters, format('%1$s = (target).%1$s', col)); 
 END LOOP;

 update_stmt := format(
 'UPDATE %s SET %s WHERE %s',
 pg_typeof(target),
 array_to_string(setters, ', '),
 array_to_string(selectors, ' AND ')
 );
 insert_stmt := format('INSERT INTO %s SELECT (target).*', pg_typeof(target));

 LOOP
 EXECUTE update_stmt; 
 IF found THEN
 RETURN;
 END IF;

 BEGIN
 EXECUTE insert_stmt;
 RETURN;
 EXCEPTION when UNIQUE_VIOLATION THEN
 -- do nothing
 END;
 END LOOP;
END;
$$
LANGUAGE plpgsql;

尝试使用这里函数时,出现了一个错误:


SELECT * FROM upsert(ROW(1,'{}')::doodad);

错误:列"目标"不存在: SELECT * FROM upsert ( 行( 1,'{}'):: 小工具)

我尝试更改upsert语句以使用占位符,但我无法确定如何使用记录调用它:


EXECUTE update_stmt USING target;

错误:没有参数 $2: SELECT * FROM upsert ( 行( 1,'{}'):: 小工具)


EXECUTE update_stmt USING target.*;

错误:查询"选择目标。*"返回 2列: SELECT * FROM upsert ( 行( 1,'{}'):: 小工具)

我觉得很接近一个解决方案但是我不知道语法问题。

时间: 原作者:

0 0

简单回答:你不能。

变量替换不会出现在给定的命令字符串中或者它的某个变体中。 如果需要将变量插入到这样的命令中,那么作为构造字符串值或者使用,的一部分。 1

更长的答案:

函数中的SQL语句和表达式可以引用函数的变量和参数。 在幕后,Pl/PgSQL 将查询参数替换为这些引用。 2

这是拼图中第一个重要的部分: Pl/PgSQL 对函数参数进行神奇变换,使它的变成变量替换。

第二个是变量替换字段可以引用:

函数的参数可以是复合类型( 完成表格行) 。 在这种情况下,对应的标识符 $n 将是行变量,并且可以从它中选择字段,例如 $1.user_id3

因为它引用了函数参数,所以我应该能够在 EXECUTE 中使用相同的语法,这一点让我迷惑。

这两个事实解锁了解决方案: 使用using子句中的行变量,并在动态SQL中取消引用它的字段。 结果( ):


CREATE OR REPLACE FUNCTION upsert(v_target ANYELEMENT)
 RETURNS SETOF ANYELEMENT AS
$$
DECLARE
 v_target_name TEXT;
 v_attr_name NAME;
 v_selectors TEXT[];
 v_colname TEXT;
 v_setters TEXT[];
 v_update_stmt TEXT;
 v_insert_stmt TEXT;
 v_temp RECORD;
BEGIN
 v_target_name := format_type(pg_typeof(v_target), NULL);

 FOR v_attr_name IN SELECT a.attname
 FROM pg_index i
 JOIN pg_attribute a ON a.attrelid = i.indrelid 
 AND a.attnum = ANY(i.indkey)
 WHERE i.indrelid = v_target_name::regclass
 AND i.indisprimary
 LOOP
 v_selectors := array_append(v_selectors, format('t.%1$I = $1.%1$I', v_attr_name));
 END LOOP;

 FOR v_colname IN SELECT json_object_keys(row_to_json(v_target))
 LOOP
 v_setters := array_append(v_setters, format('%1$I = $1.%1$I', v_colname));
 END LOOP;

 v_update_stmt := format(
 'UPDATE %I t SET %s WHERE %s RETURNING t.*',
 v_target_name,
 array_to_string(v_setters, ','),
 array_to_string(v_selectors, ' AND ')
 );

 v_insert_stmt = format('INSERT INTO %I SELECT $1.*', v_target_name);

 LOOP
 EXECUTE v_update_stmt INTO v_temp USING v_target;
 IF v_temp IS NOT NULL THEN
 EXIT;
 END IF;

 BEGIN
 EXECUTE v_insert_stmt USING v_target;
 EXIT;
 EXCEPTION when UNIQUE_VIOLATION THEN
 -- do nothing
 END;
 END LOOP;
 RETURN QUERY SELECT v_target.*;
END;
$$
LANGUAGE plpgsql;

对于可写的CTE风扇,这对CTE格式非常有用:


v_cte_stmt = format(
 'WITH up as (%s) %s WHERE NOT EXISTS (SELECT 1 from up t WHERE %s)',
 v_update_stmt,
 v_insert_stmt,
 array_to_string(v_selectors, ' AND '));

LOOP
 BEGIN
 EXECUTE v_cte_stmt USING v_target;
 EXIT;
 EXCEPTION when UNIQUE_VIOLATION THEN
 -- do nothing
 END;
END LOOP;
RETURN QUERY SELECT v_target.*;

我已经对这个解决方案进行了性能测试,并且我依赖其他人的正确性进行分析。 目前,它似乎在我的开发环境中正确运行在 9.3 。 你的方法可能是不同的。

原作者:
...