Wie Wert von composite-variable-Feld mit dynamic SQL

Schneller mit hstore

Da Postgres 9.0, mit der zusätzliche module hstore installiert, die in Ihrer Datenbank es gibt eine sehr einfache und schnelle Lösung mit der #= - operator, dass ...

ersetzen[s] Felder in record mit passenden Werten aus hstore.

Installieren Sie das Modul:

CREATE EXTENSION hstore;

Beispiele:

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

Werte müssen Stimmen, um text und zurück, offensichtlich.

Beispiel plpgsql-Funktionen mit mehr details:

• Endlose Schleife in der trigger-Funktion
• Weisen Sie auf NEU, indem der Schlüssel in einer Postgres-trigger

Fast, als schnell mit json

Gibt es ähnliches, aber derzeit ohne Papiere (S. 9.5) - Lösungen mit json (S. 9.3+) oder jsonb (pg 9.4+), gebaut in der Postgres, so brauchen Sie nicht ein zusätzliches Modul.

Siehe @Geir ' s hat die Antwort für details.

Ohne hstore und json

Wenn Sie auf eine ältere version oder kann die Installation der zusätzlichen module hstore oder kann nicht davon ausgehen, es ist installiert, hier ist eine verbesserte version von dem, was ich geschrieben zuvor. Noch langsamer als die hstore - operator, wenn:

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;

Nennen:

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

Notes

• Einen expliziten cast des Wertes _val zu der Ziel-Datentyp ist nicht notwendig, ein string-literal in der dynamischen Abfrage wäre gezwungen automatisch, dass die Unterabfrage auf pg_type. Aber ich nahm es einen Schritt weiter:

• Ersetzen quote_literal(_val) mit direkten Wert der Einfügung über die USING - Klausel. Spart einen Funktionsaufruf und zwei Würfe, und sicherer ist es ohnehin. text dazu gezwungen wird, um den Ziel-Typ automatisch in modernen PostgreSQL. (Nicht getestet mit Versionen vor 9.1.)

• array_to_string(ARRAY()) ist schneller als string_agg().

• Keine Variablen benötigt, keine DECLARE. Weniger Zuweisungen.

• Keine Unterabfrage in der dynamischen SQL. ($1).field ist schneller.

• pg_typeof(_comp_val)::text::regclass
  
  macht das gleiche wie
  
  (SELECT typrelid FROM pg_catalog.pg_type WHERE oid = pg_typeof($1)::oid)
  
  für gültige composite-Typen, nur schneller.
  
  Diese Letzte änderung basiert auf der Annahme, dass pg_type.typname ist immer identisch mit dem damit verbundenen pg_class.relname für registrierte composite-Typen, und die doppelte Besetzung kann ersetzen Sie die Unterabfrage. Ich lief in diesem test in einer großen Datenbank, um zu überprüfen, und es kam leer als erwartet:

  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

• Den Einsatz eines INOUT - parameter entfällt die Notwendigkeit für eine explizite RETURN. Dies ist nur ein notational Verknüpfung. Pavel wird nicht, wie es ist, bevorzugt er eine explizite RETURN Aussage ...

Alles zusammen ist dies fast doppelt so schnell als die Vorherige version.

Original (überholt) Antwort:

Das Ergebnis ist eine version, die ~ 2.25 mal schneller. Aber ich hätte wahrscheinlich es nicht getan haben, ohne Gebäude auf Pavel ' s zweite version.

Neben, dieser version vermeidet die meisten der casting zu text und zurück, indem Sie alles innerhalb einer einzigen Abfrage, so sollte es sein, viel weniger fehleranfällig ist.

Getestet mit PostgreSQL 9.0 und 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;

InformationsquelleAutor Erwin Brandstetter

UPDATE/Achtung: Erwin weist darauf hin, dass dies derzeit ohne Papiere, und die Handbuch zeigt an, dass es nicht möglich sein sollte, zu verändern, Datensätze auf diese Weise.

Verwenden hstore oder Pavel Lösung statt.

Diese einfache json basierte Lösung, die ist fast so schnell wie hstore und erfordert nur Postgres 9.3 oder neuer. Dies sollte eine gute option, wenn Sie nicht verwenden können, die hstore-Erweiterung, und der performance-Unterschied sollte vernachlässigbar sein. Benchmarks: https://stackoverflow.com/a/28673542/1914376

a) Wir können entweder tun es inline durch cast/concat. Json-Funktion erfordert Postgres 9.3:

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

b) oder inline mit Funktionen von Postgres 9.4.

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

Anmerkung: ich habe json_object(ARRAY[key,value]) da war er etwas schneller als json_build_object(Schlüssel,Wert):

Ausblenden der casting-details, die Sie verwenden können, a) eine Funktion, mit wenig Aufwand.

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;

InformationsquelleAutor Geir Bostad

Test-setup und benchmarks v2

Erwin ermutigt, sich zu reproduzieren, seine benchmark in diesem thread (https://stackoverflow.com/a/7782839/1914376), so dass ich geändert, seinen code mit synthetischen Testdaten und Hinzugefügt, sowohl für die hstore-Lösung und die json-Lösung aus meiner Antwort (und eine json-Lösung von Pavel gefunden in einem anderen thread) Die benchmark wird nun als eine Abfrage, so dass es leichter zu erfassen, die Ergebnisse.

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://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; $$;

Testergebnisse auf 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)

InformationsquelleAutor Geir Bostad