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

发布时间：2020-12-13 16:32:00 所属栏目：百科来源：网络整理

导读：给定这种类型： -- Just for testing purposes:CREATE TYPE testType as (name text) 我可以使用此函数动态获取字段的值： CREATE OR REPLACE FUNCTION get_field(object anyelement,field text) RETURNS text as$BODY$DECLARE value text;BEGIN EXECUTE 'SE

给定这种类型：

-- Just for testing purposes:
CREATE TYPE testType as (name text)

我可以使用此函数动态获取字段的值：

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

调用get_field(‘(david)’:: testType,’name’)按预期返回“david”.

但是如何在复合类型中设置一个字段的值？我试过这些功能：

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,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,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

和一些变化.
调用set_field_tryX不起作用.我总是得到“错误：语法错误在…附近”.
我该如何做到这一点？

笔记：

>参数是anyelement,该字段可以是复合类型中的任何字段.我不能只使用object.name.
>我关心SQL注入.任何建议在这将不胜感激,但这不是我的问题.

与商店更快

如果您的数据库中安装了additional module hstore,那么#= operator可以使用非常简单快速的解决方案

replace[s] fields in record with matching values from hstore.

SELECT my_record #= '"field"=>"value"'::hstore;  -- with string literal
SELECT my_record #= hstore(field,value);        -- with values

价值必须被转换成文本和显示.
自Postgres 9.0起可用.

几乎和json一样快

与Postgres内置的json(pg 9.3)或jsonb(pg 9.4)类似,但目前尚无文档(截至第9.5页)解决方案,因此您不需要额外的模块.

See @Geir’s added answer for details.

没有hstore和json

如果您使用的是旧版本,或者无法安装附加模块hstore,或者无法假定已安装,以下是我之前发布的改进版本.尽管如此,仍然比hstore运算符慢：

CREATE OR REPLACE FUNCTION f_setfield(INOUT _comp_val anyelement,_field text,_val text)
  RETURNS anyelement 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$LANGUAGE plpgsql STABLE;

呼叫：

CREATE TEMP TABLE t( a int,b text);  -- Composite type for testing
SELECT f_setfield(NULL::t,'a','1');

笔记

>对目标数据类型的值_val的显式转换不是必需的,动态查询中的字符串文字将被自动强制执行,从而避免了pg_type上的子查询.但我进一步说道：
>通过USING子句将直接值插入替换quote_literal(_val).保存一个函数调用和两个转换,反正更安全.文本在现代PostgreSQL中被自动强制转换为目标类型. (9.1之前没有测试版本)
> array_to_string(ARRAY())比string_agg()快.
>无需变量,无DECLARE.更少的作业.
>动态SQL中没有子查询. ($1).field更快.
> pg_typeof(_comp_val):: text :: regclass
做同样的事情
(SELECT typrelid FROM pg_catalog.pg_type WHERE oid = pg_typeof($1):: oid)
对于有效的复合类型,只需更快.
最后一个修改是基于pg_type.typname与注册的组合类型的关联的pg_class.relname相同的假设,双重转换可以替代子查询.我在一个大数据库中运行这个测试来验证,并且如预期的那样出现空：

SELECT *
FROM   pg_catalog.pg_type t
JOIN   pg_namespace  n ON n.oid = t.typnamespace
WHERE  t.typrelid > 0  -- exclude non-composite types
AND    t.typrelid IS DISTINCT FROM
      (quote_ident(n.nspname ) || '.' || quote_ident(typname))::regclass

>使用INOUT参数可以避免显式RETURN的需要.这只是一个标志性的捷径.帕维尔不喜欢,他更喜欢一个明确的RETURN语句

一切都放在一起,几乎是以前版本的两倍.

原创(过时)答案：

结果是一个版本快了2.25倍.但是,如果没有建立在Pavel的第二个版本上,我可能无法做到这一点.

此外,此版本通过在单个查询中执行所有操作来避免大部分转换为文本和返回,因此应该更容易出错.
用PostgreSQL 9.0和9.1进行测试.

CREATE FUNCTION f_setfield(_comp_val anyelement,_val text)
  RETURNS anyelement AS
$func$
DECLARE
   _list text;
BEGIN
_list := (
   SELECT string_agg(x.fld,')
   FROM  (
      SELECT CASE WHEN a.attname = $2
              THEN quote_literal($3) || '::'|| (SELECT quote_ident(typname)
                                                FROM   pg_catalog.pg_type
                                                WHERE  oid = a.atttypid)
              ELSE quote_ident(a.attname)
             END AS fld
      FROM   pg_catalog.pg_attribute a 
      WHERE  a.attrelid = (SELECT typrelid
                           FROM   pg_catalog.pg_type
                           WHERE  oid = pg_typeof($1)::oid)
      AND    a.attnum > 0
      AND    a.attisdropped = false
      ORDER  BY a.attnum
      ) x
   );

EXECUTE 'SELECT ' || _list || ' FROM  (SELECT $1.*) x'
USING  $1
INTO   $1;

RETURN $1;
END
$func$LANGUAGE plpgsql STABLE;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!