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

概述给定这种类型： -- 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 给定这种类型：

-- 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 ."' || 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 anyelementas$BODY$DECLARE    value text;BEGIN    EXECUTE '."' || fIEld || '" := '      USING object,value;    return object;END;$BODY$LANGUAGE plpgsqlCREATE OR REPLACE FUNCTION set_fIEld_try2(object anyelement,value text)RETURNS anyelementas$BODY$DECLARE    value text;BEGIN    EXECUTE 'SELECT  INTO ."' || fIEld || '"'      USING value,object;    return object;END;$BODY$LANGUAGE plpgsqlCREATE OR REPLACE FUNCTION set_fIEld_try3(object anyelement,value text)RETURNS anyelementas$BODY$DECLARE    value text;BEGIN    EXECUTE 'BEGIN ."' || fIEld || '" := ; SELECT ; 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 literalSELECT 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$BEGINEXECUTE 'SELECT ' || array_to_string(ARRAY(      SELECT CASE WHEN attname = _fIEld                THEN ''                ELSE '().' || 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,_valINTO   _comp_val;END$func$LANGUAGE plpgsql Stable;

呼叫：

CREATE TEMP table t( a int,b text);  -- Composite type for testingSELECT 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 tJOIN   pg_namespace  n ON n.oID = t.typnamespaceWHERE  t.typrelID > 0  -- exclude non-composite typesAND    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 =               THEN quote_literal() || '::'|| (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()::oID)      AND    a.attnum > 0      AND    a.attisdropped = false      ORDER  BY a.attnum      ) x   );EXECUTE 'SELECT ' || _List || ' FROM  (SELECT .*) x'USING  INTO   ;RETURN ;END$func$LANGUAGE plpgsql Stable;

总结

以上是内存溢出为你收集整理的postgresql – 如何使用动态SQL设置复合变量字段的值全部内容，希望文章能够帮你解决postgresql – 如何使用动态SQL设置复合变量字段的值所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。