如何使用动态SQL设置复合变量字段的值

20 浏览
0 Comments

如何使用动态SQL设置复合变量字段的值

给定以下类型：\n

-- 仅用于测试目的：
CREATE TYPE testType as (name text)

\n我可以使用这个函数动态获取字段的值：\n

CREATE OR REPLACE FUNCTION get_field(object anyelement, field text) RETURNS text as
$BODY$
DECLARE
    value text;
BEGIN
    EXECUTE 'SELECT $1."' || field || '"'
      USING object
       INTO value;
    return value;
END;
$BODY$
LANGUAGE plpgsql

\n调用 get_field(\'(david)\'::testType, \'name\') 会按预期返回 \"david\"。\n但是如何设置复合类型中的字段的值呢？我尝试了以下这些函数：\n

CREATE OR REPLACE FUNCTION set_field_try1(object anyelement, field text, value text)
RETURNS anyelement
as
$BODY$
DECLARE
    value text;
BEGIN
    EXECUTE '$1."' || field || '" := $2'
      USING object, value;
    return object;
END;
$BODY$
LANGUAGE plpgsql
CREATE OR REPLACE FUNCTION set_field_try2(object anyelement, field text, value text)
RETURNS anyelement
as
$BODY$
DECLARE
    value text;
BEGIN
    EXECUTE 'SELECT $1 INTO $2."' || field || '"'
      USING value, object;
    return object;
END;
$BODY$
LANGUAGE plpgsql
CREATE OR REPLACE FUNCTION set_field_try3(object anyelement, field text, value text)
RETURNS anyelement
as
$BODY$
DECLARE
    value text;
BEGIN
    EXECUTE 'BEGIN $1."' || field || '" := $2; SELECT $1; END;'
       INTO object
      USING value, object;
    return object;
END;
$BODY$
LANGUAGE plpgsql

\n以及一些变体。\n调用 set_field_tryX 不起作用。我总是得到 \"ERROR: syntax error at or near...\"。\n我该如何实现这个功能？\n注：\n

    \n

  • 参数是 anyelement，字段可以是复合类型中的任何字段。我不能只使用 object.name。

    • \n

  • 我担心 SQL 注入问题。对此方面的任何建议都将不胜感激，但这不是我的问题。

    • \n

0

3 答案

0 Comments

问题的原因是，在PostgreSQL中使用动态SQL设置复合变量字段的值时，目前尚未正式记录该方法，且官方文档指出不应该通过这种方式修改记录。解决方法是使用hstore或Pavel的解决方案。如果不能使用hstore扩展，并且性能差异可以忽略不计，那么这个基于json的简单解决方案几乎和hstore一样快速，只需要Postgres 9.3或更新的版本。

解决方法a是通过类型转换/拼接的方式内联实现。Json函数要求Postgres 9.3：

SELECT json_populate_record( 
     record
    , ('{"'||'key'||'":"'||'new-value'||'"}')::json
);

解决方法b是通过使用Postgres 9.4的函数内联实现。

SELECT json_populate_record (
      record
     ,json_object(ARRAY['key', 'new-value'])
);

注意：我选择了json_object(ARRAY[key,value])，因为它比json_build_object(key,value)稍微快一些。

为了隐藏类型转换细节，可以将解决方法a封装在一个函数中，开销很小。

CREATE FUNCTION x.setfield_json(in_element anyelement, key text, value text)
    RETURNS anyelement AS
$BODY$
    SELECT json_populate_record( in_element, ('{"'||key||'":"'||value||'"}')::json);
$BODY$ LANGUAGE sql;

需要注意的是，自Postgres 13版本开始，这个方法已经被记录在官方文档中。

0
0 Comments

问题的出现原因：在使用动态SQL设置复合变量字段的值时，出现了一些bug和错误，导致函数无法正常工作。

解决方法：根据问题的具体情况进行了以下修复：

1. 修复了没有对类型进行quote_ident()的bug，以防止在含有模式的类型中出现问题。

2. 修复了单引号重复的bug，使用两个单引号来表示单引号，以避免转义字符串。

3. 对于具有模式的类型，需要分别对模式和类型名进行quote_ident()，如"myschema"."mytype"。

4. 对于含有已删除列的表，尝试在该表上运行VACUUM FULL。

修复后，函数可以正常工作，并且可以在触发器中调用。但在触发器中使用SELECT setfield2(NEW, MY_FIELD, "new_value") INTO _tmp语句时，无法将结果正确赋值给NEW.MY_FIELD，会出现42804错误。可能是因为表中存在已删除的列导致的问题。可以尝试在该表上运行VACUUM FULL来解决这个问题。

0
0 Comments

问题的出现的原因是需要通过动态SQL设置复合变量字段的值。解决方法有以下几种：

1. 使用hstore模块：在Postgres 9.0及更高版本中，可以安装hstore模块，然后使用#=运算符来替换record中的字段值。安装hstore模块的方法为CREATE EXTENSION hstore; 示例代码如下：

SELECT my_record #= '"field"=>"value"'::hstore; -- 使用字符串字面量

SELECT my_record #= hstore(field, value); -- 使用变量值

2. 使用json/jsonb：在Postgres 9.3及更高版本中，可以使用json_populate_record函数来替换字段值。示例代码如下：

SELECT json_populate_record (my_record, json_build_object('key', 'new-value');

3. 不使用hstore和json：如果无法安装hstore模块或不能假设已安装该模块，则可以使用改进的方法来设置字段值。示例代码如下：

CREATE OR REPLACE FUNCTION f_setfield(INOUT _comp_val anyelement, _field text, _val text)

RETURNS anyelement

LANGUAGE plpgsql STABLE AS

$func$

BEGIN

EXECUTE 'SELECT ' || array_to_string(ARRAY(

SELECT CASE WHEN attname = _field

THEN '$2'

ELSE '($1).' || quote_ident(attname)

END AS fld

FROM pg_catalog.pg_attribute

WHERE attrelid = pg_typeof(_comp_val)::text::regclass

AND attnum > 0

AND attisdropped = FALSE

ORDER BY attnum

), ',')

USING _comp_val, _val

INTO _comp_val;

END

$func$;

调用方法为：

CREATE TEMP TABLE t( a int, b text); -- Composite type for testing

SELECT f_setfield(NULL::t, 'a', '1');

以上就是关于如何使用动态SQL设置复合变量字段值的原因和解决方法。

0