Test setup and benchmarks v2
Erwin encouraged to reproduce his benchmark in this thread (https://mcmap.net/q/28915/-how-to-set-value-of-composite-variable-field-using-dynamic-sql), 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.
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 $1.*;
execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3);
select into $1 * from aux;
drop table aux;
return $1;
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($1)::oid)
LOOP
IF _name = $2 THEN
_value := $3;
ELSE
EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;
END IF;
_values[_attnum] := COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
END LOOP;
EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1;
RETURN $1;
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 = $2
THEN quote_literal($3)
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($1)::oid)
ORDER BY a.attnum
) x
);
EXECUTE '
SELECT ' || _list || '
FROM (SELECT $1.*) x'
USING $1
INTO $1;
RETURN $1;
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 '$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;
-- Pavel 3: json. (Postgres 9.4)
-- Found here: https://mcmap.net/q/28940/-postgresql-modifying-fields-dynamically-in-new-record-in-a-trigger-function
--------------------------------------------------------------------------------------------------
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
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)
