With

With

关注
发信
关注(28)粉丝(399)

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

本文介绍了如何使用动态SQL设置复合变量字段的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

给出此类型:

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

我可以使用以下函数动态获取字段的值:

I can get the value of a field dynamically with this function:

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')可以按预期方式返回大卫".

Calling get_field('(david)'::testType, 'name') works as expected returning "david".

但是如何设置复合类型的字段的值?我已经尝试过以下功能:

But how can I set a value of a field in a composite type? I've tried these functions:

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

和一些变体.调用set_field_tryX不起作用.我总是收到错误:...附近的语法错误".我该怎么做?

and some variations.Calling set_field_tryX doesn't work. I always get "ERROR: syntax error at or near...".How can I accomplish this?

注意:

  • 参数为anyelement，并且该字段可以是复合类型中的任何字段.我不能只使用object.name.
  • 我担心SQL注入.任何建议，将不胜感激，但这不是我的问题.
  • The parameter is anyelement and the field can be any field in the composite type. I can't just use object.name.
  • I'm concerned about SQL injection. Any advice in this would be appreciated but it is not my question.

推荐答案

使用hstore

更快

自Postgres 9.0 起，使用在您的数据库中安装了附加模块 hstore ，使用 #= 运算符 ...

Faster with hstore

Since Postgres 9.0, with the additional module hstore installed in your database there is a very simple and fast solution with the #= operator that ...

要安装模块:

CREATE EXTENSION hstore;

示例:

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

显然，必须将值强制转换为text.

Values have to be cast to text and back, obviously.

示例plpgsql函数具有更多详细信息:

Example plpgsql functions with more details:

  • Endless loop in trigger function
  • Assign to NEW by key in a Postgres trigger

Postgres中内置了类似的，但目前尚无记录的解决方案(自pg 9.5起)和json(pg 9.3+)或jsonb(pg 9.4+)的解决方案，因此您不需要其他模块

There are similar, but currently undocumented (as of pg 9.5) solutions with json (pg 9.3+) or jsonb (pg 9.4+), built into the Postgres, so you don't need an additional module.

有关详细信息，请参见@Geir添加的答案.

如果您使用的是旧版本，或者无法安装附加模块hstore，或者无法假定已安装附加模块，则这是我先前发布的内容的改进版本.但是，它仍然比hstore运算符慢:

If you are on an older version or cannot install the additional module hstore or cannot assume it's installed, here is an improved version of what I posted previously. Still slower than the hstore operator, though:

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上的子查询.但我又走了一步:

    Notes

    • An explicit cast of the value _val to the target data type is not necessary, a string literal in the dynamic query would be coerced automatically, obviating the subquery on pg_type. But I took it one step further:

      通过USING子句直接插入值替换quote_literal(_val).节省一个函数调用和两个强制类型转换，并且更安全.在现代PostgreSQL中，text被自动强制为目标类型. (未测试9.1之前的版本.)

      Replace quote_literal(_val) with direct value insertion via the USING clause. Saves one function call and two casts, and is safer anyway. text is coerced to the target type automatically in modern PostgreSQL. (Did not test with versions before 9.1.)

      array_to_string(ARRAY())比string_agg()快.

      不需要变量，不需要DECLARE.更少的作业.

      No variables needed, no DECLARE. Fewer assignments.

      动态SQL中没有子查询. ($1).field更快.

      No subquery in the dynamic SQL. ($1).field is faster.

      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相同，并且双精度强制转换可以替换子查询.我在一个大数据库中运行了该测试以进行验证，结果按预期空了:

      pg_typeof(_comp_val)::text::regclass
      does the same as
      (SELECT typrelid FROM pg_catalog.pg_type WHERE oid = pg_typeof($1)::oid)
      for valid composite types, just faster.
      This last modification is built on the assumption that pg_type.typname is always identical to the associated pg_class.relname for registered composite types, and the double cast can replace the subquery. I ran this test in a big database to verify, and it came up empty as expected:

      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的需要.这只是一个符号快捷方式.帕维尔(Pavel)不喜欢它，他更喜欢一个明确的RETURN陈述...

    • The use of an INOUT parameter obviates the need for an explicit RETURN. This is just a notational shortcut. Pavel won't like it, he prefers an explicit RETURN statement ...

      所有内容汇总在一起的速度几乎是以前版本的两倍.

      Everything put together this is almost twice as fast as the previous version.

      结果是一个版本快了〜2.25倍.但是，如果不基于Pavel的第二个版本，我可能无法做到这一点.

      The result is a version that's ~ 2.25 times faster. But I probably couldn't have done it without building on Pavel's second version.

      此外，此版本通过在单个查询中完成所有操作，避免了大多数文本转换，因此应该减少出错的可能性.
      经过 PostgreSQL 9.0和9.1 的测试.

      In addition, this version avoids most of the casting to text and back by doing everything within a single query, so it should be much less error prone.
      Tested with PostgreSQL 9.0 and 9.1.

      CREATE FUNCTION f_setfield(_comp_val anyelement, _field text, _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;

      这篇关于如何使用动态SQL设置复合变量字段的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

10-15 13:41
Powered by LMLPHP ©2024 With 0.001774