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

How to set value of composite variable field using dynamic SQL

提问by DavidEG

Given this type:

鉴于这种类型：

-- 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 ."' || field || '"'
      USING object
       INTO value;

    return value;
END;
$BODY$
LANGUAGE plpgsql

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

调用get_field('(david)'::testType, 'name')按预期工作返回“大卫”。

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 '."' || field || '" := '
      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  INTO ."' || 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 ."' || field || '" := ; SELECT ; END;'
       INTO object
      USING value, object;

    return object;
END;
$BODY$
LANGUAGE plpgsql

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

和一些变化。调用set_field_tryX不起作用。我总是收到“错误：语法错误在或附近......”。我怎样才能做到这一点？

Notes:

笔记：

• The parameter is anyelementand 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.

• 参数是anyelement，字段可以是复合类型中的任何字段。我不能只使用 object.name。
• 我担心 SQL 注入。对此的任何建议将不胜感激，但这不是我的问题。

回答by Erwin Brandstetter

Faster with hstore

用更快 hstore

Since Postgres 9.0, with the additional module hstoreinstalled in your database there is a very simple and fast solution with the #=operatorthat ...

从 Postgres 9.0 开始，在您的数据库中安装了附加模块后hstore，#=操作员提供了一个非常简单和快速的解决方案......

replace[s] fields in recordwith matching values from hstore.

用record来自 的匹配值替换 [s] 字段hstore。

To install the module:

安装模块：

CREATE EXTENSION hstore;

Examples:

例子：

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

Values have to be cast to textand back, obviously.

text显然，值必须来回转换。

Example plpgsql functions with more details:

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

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

• 触发函数中的无限循环
• 通过 Postgres 触发器中的键分配给 NEW

Almost as fast with json

几乎一样快 json

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.

有类似的，但目前未记录（从 pg 9.5 开始）的解决方案，带有json(pg 9.3+) 或jsonb(pg 9.4+)，内置在 Postgres 中，因此您不需要额外的模块。

See @Geir's added answer for details.

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

Without hstoreand json

没有hstore和json

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

如果您使用的是旧版本或无法安装附加模块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 ''
                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, _val
INTO   _comp_val;

END
$func$ LANGUAGE plpgsql STABLE;

Call:

称呼：

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

Notes

笔记

• An explicit cast of the value _valto 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:

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

• array_to_string(ARRAY())is faster than string_agg().

• No variables needed, no DECLARE. Fewer assignments.

• No subquery in the dynamic SQL. ($1).fieldis faster.

• 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.typnameis always identical to the associated pg_class.relnamefor 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
  

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

• _val不需要将值显式转换为目标数据类型，动态查询中的字符串文字将被自动强制转换，从而避免在 上的子查询pg_type。但我更进一步：

• 替换quote_literal(_val)为通过USING子句直接插入值。保存一次函数调用和两次强制转换，无论如何都更安全。text在现代 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陈述......

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

一切都放在一起，这几乎是以前版本的两倍。

---

---

Original (outdated) answer:

原始（过时）答案：

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.

结果是版本快了约 2.25 倍。但如果没有建立在 Pavel 的第二个版本上，我可能无法做到。

In addition, this version avoids most of the castingto 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.

此外，此版本通过在单个查询中执行所有操作避免了大部分转换为文本和返回，因此它应该更不容易出错。
使用PostgreSQL 9.0 和 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 = 
              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;

回答by Pavel Stehule

I wrote a second version of setfield function. It work on postgres 9.1I didn't test it on older versions. It's not a miracle (from performance view), but it is more robust and about 8 times faster than the previous.

我写了 setfield 函数的第二个版本。它适用于postgres 9.1我没有在旧版本上测试它。这不是一个奇迹（从性能角度来看），但它更健壮，并且比以前快了大约 8 倍。

CREATE OR REPLACE FUNCTION public.setfield2(anyelement, text, text)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
DECLARE 
  _name text;
  _values text[];
  _value text;
  _attnum int;
BEGIN
  FOR _name, _attnum
     IN SELECT a.attname, a.attnum
          FROM pg_catalog.pg_attribute a 
         WHERE a.attrelid = (SELECT typrelid
                               FROM pg_type
                              WHERE oid = pg_typeof()::oid)
           AND a.attnum > 0 
  LOOP
    IF _name =  THEN
      _value := ;
    ELSE
      EXECUTE 'SELECT (().' || quote_ident(_name) || ')::text' INTO _value USING ;
    END IF;
    _values[_attnum] :=  COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', ''); 
  END LOOP;
  EXECUTE 'SELECT (' || quote_ident(pg_typeof()::text) || ' ''(' || array_to_string(_values,',') || ')'').*' INTO ; 
  RETURN ;
END;
$function$;

回答by Geir Bostad

UPDATE/caution:Erwin points out that this is currently undocumented, and the manualindicates it should not be possible to alter records this way.

更新/警告：Erwin 指出这是当前未记录的，并且手册指出不应该以这种方式更改记录。

Use hstore or Pavel's solutioninstead.

请改用 hstore 或Pavel 的解决方案。

This simple json based solution is almost as fast as hstore, and requires only Postgres 9.3or newer. This should be a good option if you can't use the hstore extension, and the performance difference should be negligible. Benchmarks: https://stackoverflow.com/a/28673542/1914376

这个简单的基于 json 的解决方案几乎和 hstore 一样快，并且只需要 Postgres 9.3或更新版本。如果您不能使用 hstore 扩展，这应该是一个不错的选择，并且性能差异应该可以忽略不计。基准：https: //stackoverflow.com/a/28673542/1914376

a) We can either do it inline by cast/concat. Json function requires Postgres 9.3:

a) 我们可以通过 cast/concat 内联进行。Json 函数需要 Postgres 9.3：

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

b) or inline by using functions from Postgres 9.4.

b) 或使用 Postgres 9.4 中的函数内联。

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

Note: I chose json_object(ARRAY[key,value]) since it was a bit faster than json_build_object(key,value):

注意：我选择了 json_object(ARRAY[key,value]) 因为它比 json_build_object(key,value) 快一点：

To hide the casting details you can use a) in a function, with little overhead.

要隐藏转换细节，您可以在函数中使用 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;

回答by Pavel Stehule

"SELECT INTO" outside plpgsql (in dynamic SQL context) has different sense than you expect - it store a result of query to table.

plpgsql 之外的“SELECT INTO”（在动态 SQL 上下文中）的意义与您预期的不同 - 它将查询结果存储到表中。

Modification of any field is possible, but not simple

可以修改任何字段，但并不简单

CREATE OR REPLACE FUNCTION public.setfield(a anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
  create temp table aux as select .*;
  execute 'update aux set ' || quote_ident() || ' = ' || quote_literal();
  select into  * from aux;
  drop table aux;
  return ;
end;
$function$

But this code is not very effective - is not possible to write this well in plpgsql. You can find some C library, that should to do.

但是这段代码不是很有效 - 在 plpgsql 中不可能写得很好。你可以找到一些 C 库，那应该可以。

回答by Erwin Brandstetter

Update March 2015:
Largely outdated now. Consider the new benchmark by @Geirwith faster variants.

2015 年 3 月更新：
现在已经过时了。考虑@Geir 的具有更快变体的新基准。

---

---

Test setup and benchmarks

测试设置和基准

I took the three solutions presented (by Oct. 16th, 2011) and ran a test on PostgreSQL 9.0. You find the complete setup below. Only test data are not included as I used a real life database (not synthetic data). It's all encapsulated in its own schema for non-intrusive use.

我采用了提出的三个解决方案（截至 2011 年 10 月 16 日）并在 PostgreSQL 9.0 上进行了测试。您可以在下面找到完整的设置。由于我使用的是现实生活中的数据库（不是合成数据），因此仅不包括测试数据。所有这些都封装在自己的架构中，以供非侵入性使用。

I would like to encourage anybody who wants to reproduce the test. Maybe with postgres 9.1? And add your results here? :)

我想鼓励任何想要重现测试的人。也许使用 postgres 9.1？并在此处添加您的结果？:)

-- DROP SCHEMA x CASCADE;
CREATE SCHEMA x;

-- Pavel 1
CREATE OR REPLACE FUNCTION x.setfield(anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
  create temp table aux as select .*;
  execute 'update aux set ' || quote_ident() || ' = ' || quote_literal();
  select into  * from aux;
  drop table aux;
  return ;
end;
$function$;

-- Pavel 2 (with patches)
CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
DECLARE 
  _name text;
  _values text[];
  _value text;
  _attnum int;
BEGIN
  FOR _name, _attnum
     IN SELECT a.attname, a.attnum
           FROM pg_catalog.pg_attribute a 
          WHERE a.attrelid = (SELECT typrelid
                                 FROM pg_type
                                WHERE oid = pg_typeof()::oid) 
  LOOP
    IF _name =  THEN
      _value := ;
    ELSE
      EXECUTE 'SELECT (().' || quote_ident(_name) || ')::text' INTO _value USING ;
    END IF;
    _values[_attnum] :=  COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
  END LOOP;
  EXECUTE 'SELECT (' || pg_typeof()::text || '''(' || array_to_string(_values,',') || ')'').*' INTO ; 
  RETURN ;
END;
$function$;

-- Erwin 1
CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
 _list text;

BEGIN
_list := (
   SELECT string_agg(x.fld, ',')
   FROM   (
      SELECT CASE WHEN a.attname = 
              THEN quote_literal()
              ELSE quote_ident(a.attname)
             END AS fld
      FROM   pg_catalog.pg_attribute a 
      WHERE  a.attrelid = (SELECT typrelid
                           FROM   pg_type
                           WHERE  oid = pg_typeof()::oid) 
      ORDER BY a.attnum
   ) x
);

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

RETURN ;
END;
$body$ LANGUAGE plpgsql;

-- composite type for tests.
CREATE TYPE x.t_f as (
 id       int
,company  text
,sort     text
,log_up   timestamp 
,log_upby smallint
);

-- temp table with real life test data
DROP   TABLE IF EXISTS tmp_f;
CREATE TEMP table tmp_f AS 
   SELECT ROW(firma_id,firma,sort,log_up,log_upby)::x.t_f AS f
   FROM   ef.firma
   WHERE  firma !~~ '"%';

-- SELECT count(*) FROM tmp_f;  -- 5183

-- Quick test: results are identical?
SELECT *,
       x.setfield (f, 'company','test')
      ,x.setfield2(f, 'company','test')
      ,x.setfield3(f, 'company','test')
 FROM tmp_f
LIMIT 10;

Benchmarks

基准

I ran the queries a couple of times to populate the cache. The presented results are the best of five total runtimes with EXPLAIN ANALYZE.

我运行了几次查询以填充缓存。显示的结果是五个总运行时间中最好的一个EXPLAIN ANALYZE。

Rirst round with 1000 rows

第一轮 1000 行

Pavel's first prototype maxes out shared memory with more rows.

Pavel 的第一个原型使用更多行最大化共享内存。

Pavel 1: 2445.112 ms

帕维尔 1：2445.112 毫秒

SELECT x.setfield (f, 'company','test') FROM tmp_f limit 1000;

Pavel 2: 263.753 ms

帕维尔 2：263.753 毫秒

SELECT x.setfield2(f, 'company','test') FROM tmp_f limit 1000;

Erwin 1: 120.671 ms

埃尔文 1：120.671 毫秒

SELECT x.setfield3(f, 'company','test') FROM tmp_f limit 1000;

Another test with 5183 rows.

5183 行的另一个测试。

Pavel 2: 1327.429 ms

保尔 2：1327.429 毫秒

SELECT x.setfield2(f, 'company','test') FROM tmp_f;

Erwin1: 588.691 ms

Erwin1：588.691 毫秒

SELECT x.setfield3(f, 'company','test') FROM tmp_f;

回答by Geir Bostad

Test setup and benchmarks v2

测试设置和基准测试 v2

Erwin encouraged to reproduce his benchmark in this thread (https://stackoverflow.com/a/7782839/1914376), so I modified his code with synthetic test data and added both the hstore solution and the json-solution from my answer (and a json solution by Pavel found in another thread) The benchmark is now run as one query, making it easier to capture the results.

Erwin 鼓励在这个线程（https://stackoverflow.com/a/7782839/1914376）中重现他的基准，所以我用合成测试数据修改了他的代码，并从我的答案中添加了 hstore 解决方案和 json-solution（和Pavel 在另一个线程中找到的 json 解决方案）基准现在作为一个查询运行，从而更容易捕获结果。

DROP SCHEMA IF EXISTS x CASCADE;
CREATE SCHEMA x;


-- Pavel 1:
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION x.setfield(anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
  create temp table aux as select .*;
  execute 'update aux set ' || quote_ident() || ' = ' || quote_literal();
  select into  * from aux;
  drop table aux;
  return ;
end;
$function$;


-- Pavel 2 (with patches)
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
DECLARE
  _name text;
  _values text[];
  _value text;
  _attnum int;
BEGIN
  FOR _name, _attnum
     IN SELECT a.attname, a.attnum
           FROM pg_catalog.pg_attribute a
          WHERE a.attrelid = (SELECT typrelid
                                 FROM pg_type
                                WHERE oid = pg_typeof()::oid)
  LOOP
    IF _name =  THEN
      _value := ;
    ELSE
      EXECUTE 'SELECT (().' || quote_ident(_name) || ')::text' INTO _value USING ;
    END IF;
    _values[_attnum] :=  COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
  END LOOP;
  EXECUTE 'SELECT (' || pg_typeof()::text || '''(' || array_to_string(_values,',') || ')'').*' INTO ;
  RETURN ;
END;
$function$;


-- Erwin 1
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
 _list text;

BEGIN
_list := (
   SELECT string_agg(x.fld, ',')
   FROM   (
      SELECT CASE WHEN a.attname = 
              THEN quote_literal()
              ELSE quote_ident(a.attname)
             END AS fld
      FROM   pg_catalog.pg_attribute a
      WHERE  a.attrelid = (SELECT typrelid
                           FROM   pg_type
                           WHERE  oid = pg_typeof()::oid)
      ORDER BY a.attnum
   ) x
);

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

RETURN ;
END;
$body$ LANGUAGE plpgsql;


-- Erwin 2
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION x.setfield4(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 ''
                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, _val
INTO   _comp_val;

END
$func$ LANGUAGE plpgsql;


-- Pavel 3: json. (Postgres 9.4)
-- Found here: https://stackoverflow.com/a/28284491/1914376
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION x.setfield5(r anyelement, fn text, val text,OUT result anyelement)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
declare jo json;
begin
  jo := (select json_object(array_agg(key), 
                            array_agg(case key when fn then val
                                               else value end)) 
            from json_each_text(row_to_json(r)));
  result := json_populate_record(r, jo);
end;
$function$;


-- Json. Use built-in json functions (Postgres 9.3)
-- This is available from 9.3 since we create json by casting 
-- instead of using json_object/json_build_object only available from 9.4
--------------------------------------------------------------------------------------------------
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;



--------------------------------------------------------------------------------------------------
-- Test setup
--------------------------------------------------------------------------------------------------

-- composite type for tests.
CREATE TYPE x.t_f as (
 id       int
,company  text
,sort     text
,log_up   timestamp
,log_upby smallint
);

-- Create temp table with synthetic test data
DROP TABLE IF EXISTS tmp_f;
CREATE TEMP table tmp_f AS
   SELECT ROW(i, 'company'||i, NULL, NULL, NULL)::x.t_f AS f
   FROM generate_series(1, 5000) S(i);



-- Run the benchmark
DO $$  DECLARE  start_time timestamptz; test_count integer; test_description TEXT; BEGIN

    test_count := 200;
    test_description := 'setfield, Pavel 1: temptable';
    start_time := clock_timestamp();    
    PERFORM x.setfield (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
    RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;

    test_count := 5000;
    test_description := 'setfield2, Pavel 2: reflection';
    start_time := clock_timestamp();
    PERFORM x.setfield2 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
    RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;

    test_count := 5000;
    test_description := 'setfield3, Erwin 1: reflection';
    start_time := clock_timestamp();
    PERFORM x.setfield3 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
    RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;

    test_count := 5000;
    test_description := 'setfield4, Erwin 2: reflection';
    start_time := clock_timestamp();
    PERFORM x.setfield4 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
    RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;

    test_count := 5000;
    test_description := 'setfield5, Pavel 3: json (PG 9.4)';
    start_time := clock_timestamp();
    PERFORM x.setfield5 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
    RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;

    test_count := 5000;
    test_description := 'setfield_json, Geir 1: casting (PG 9.3)';
    start_time := clock_timestamp();
    PERFORM x.setfield_json (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
    RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;

    --json_object(ARRAY(key,value]) is actually faster than json_build_object(key, value)
    test_count := 5000;
    test_description := 'no function/inlined: json_object (PG 9.4)';
    start_time := clock_timestamp();
    PERFORM json_populate_record( f, json_object(ARRAY['company', 'new-value'||md5(random()::text)]  )) FROM tmp_f LIMIT test_count;
    RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;

    test_count := 5000;
    test_description := 'no function/inlined: hstore (PG 9.0)';
    start_time := clock_timestamp();
    PERFORM f #= hstore('company', 'new-value'||md5(random()::text))  FROM tmp_f LIMIT test_count;
    RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;

END; $$;

Test results on 9.4.1, win32, i5-4300U

在 9.4.1、win32、i5-4300U 上的测试结果

NOTICE:  Test took: 1138 ms (for 200 rows) Name: setfield, Pavel 1: temptable
NOTICE:  Test took: 652 ms (for 5000 rows) Name: setfield2, Pavel 2: reflection
NOTICE:  Test took: 364 ms (for 5000 rows) Name: setfield3, Erwin 1: reflection
NOTICE:  Test took: 275 ms (for 5000 rows) Name: setfield4, Erwin 2: reflection
NOTICE:  Test took: 192 ms (for 5000 rows) Name: setfield5, Pavel 3: json (PG 9.4)
NOTICE:  Test took: 23 ms (for 5000 rows) Name: setfield_json, Geir 1: casting (PG 9.3)
NOTICE:  Test took: 25 ms (for 5000 rows) Name: no function/inlined: json_object (PG 9.4)
NOTICE:  Test took: 14 ms (for 5000 rows) Name: no function/inlined: hstore (PG 9.0)