AUSFÜHREN...IN...USING-Anweisung in PL/pgSQL nicht erfüllen können, in einen Satz?

Einfachere alternative zu deiner Antwort gepostet. Sollte deutlich besser.

Diese Funktion ruft eine Zeile aus einer bestimmten Tabelle (in_table_name) und primary key-Wert (in_row_pk), und fügt Sie als neue Zeile in der gleichen Tabelle, mit einige Werte ersetzt (in_override_values). Die neue primary key-Wert, der standardmäßig zurückgegeben wird (pk_new).

CREATE OR REPLACE FUNCTION f_clone_row(in_table_name regclass
                                     , in_row_pk int
                                     , in_override_values hstore
                                     , OUT pk_new int) AS
$func$
DECLARE
   _pk   text;  -- name of PK column
   _cols text;  -- list of names of other columns
BEGIN

-- Get name of PK column
SELECT INTO _pk  a.attname
FROM   pg_catalog.pg_index     i
JOIN   pg_catalog.pg_attribute a ON a.attrelid = i.indrelid
                                AND a.attnum   = i.indkey[0]  -- 1 PK col!
WHERE  i.indrelid = 't'::regclass
AND    i.indisprimary;

-- Get list of columns excluding PK column
_cols := array_to_string(ARRAY(
      SELECT quote_ident(attname)
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = in_table_name -- regclass used as OID
      AND    attnum > 0               -- exclude system columns
      AND    attisdropped = FALSE     -- exclude dropped columns
      AND    attname <> _pk           -- exclude PK column
      ), ',');

-- INSERT cloned row with override values, returning new PK
EXECUTE format('
   INSERT INTO %1$I (%2$s)
   SELECT %2$s
   FROM  (SELECT (t #= $1).* FROM %1$I t WHERE %3$I = $2) x
   RETURNING %3$I'
 , in_table_name, _cols, _pk)
USING   in_override_values, in_row_pk -- use override values directly
INTO    pk_new;                       -- return new pk directly

END
$func$ LANGUAGE plpgsql;

Nennen:

SELECT f_clone_row('t', 1, '"col1"=>"foo_new","col2"=>"bar_new"'::hstore);

SQL Fiddle.

• Verwenden regclass als input-parameter geben, so dass nur gültige Tabellennamen verwendet werden können, um mit zu beginnen und SQL-Injektion ist ausgeschlossen. Die Funktion auch nicht früher und anmutig sollten Sie eine illegale Tabelle Namen.

• Verwenden Sie ein OUT parameter (pk_new) zur Vereinfachung der syntax.

• Keine Notwendigkeit, um herauszufinden, den nächsten Wert für den Primärschlüssel manuell. Es wird automatisch eingefügt und kehrte nach der Tat. Das ist nicht nur einfacher und schneller, Sie vermeiden auch, verschwendet wird oder out-of-order-Sequenz-Nummern.

• Verwenden format() zur Vereinfachung der Montage der dynamische query-string und macht es weniger fehleranfällig. Beachten Sie, wie ich verwenden positionsgebundenen Parameter für Bezeichner und strings bzw.

• Baue ich auf Ihre impliziten Annahme erlaubt, dass die Tische haben eine eine einzige primary key-Spalte des Typs integer mit einer Spalte Standard. In der Regel serial Spalten.

• Zentrales element der Funktion ist die Letzte INSERT:

  • Merge, override-Werte mit der vorhandenen Zeile mit der #= - operator in einer untergeordneten select-und zerlegen Sie die resultierende Zeile.
  • Dann können Sie nur die relevanten Spalten in der main SELECT.
  • Lassen Postgres Zuweisung des Standard-Wertes für den PK und erhalten Sie es zurück mit der RETURNING - Klausel.
  • Schreiben der zurückgegebene Wert in die OUT parameter direkt.
  • Alles geschieht in einem einzigen SQL-Befehl, das ist in der Regel am schnellsten.

InformationsquelleAutor Erwin Brandstetter