Wstaw z dynamiczną nazwą tabeli w funkcji wyzwalania

Nie jestem pewien, jak osiągnąć coś takiego: 

CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$
    DECLARE
        shadowname varchar := TG_TABLE_NAME || 'shadow';
    BEGIN
        INSERT INTO shadowname VALUES(OLD.*);
        RETURN OLD;
    END;
$$
LANGUAGE plpgsql;

Czyli wstawianie wartości do tabeli o dynamicznie generowanej nazwie.
Wykonanie powyższego kodu daje: 

ERROR:  relation "shadowname" does not exist
LINE 1: INSERT INTO shadowname VALUES(OLD.*)

Wydaje się sugerować, że zmienne nie są rozwijane/dozwolone jako nazwy tabel. Nie znalazłem odniesienia do tego w instrukcji Postgres.

Już eksperymentowałem z EXECUTE tak: 

  EXECUTE 'INSERT INTO ' || quote_ident(shadowname) || ' VALUES ' || OLD.*;

Ale bez powodzenia: 

ERROR:  syntax error at or near ","
LINE 1: INSERT INTO personenshadow VALUES (1,sven,,,)

Typ RECORD wydaje się być utracony: OLD.* wydaje się być konwertowane na łańcuch znaków i reparsowane get, co prowadzi do różnego rodzaju problemów z typem (np. wartości NULL).

Jakieś pomysły?
Author: Erwin Brandstetter, 2011-10-27
Source

2 answers

PostgreSQL 9.1 lub nowszy

format() posiada wbudowany sposób ucieczki identyfikatorów. Prostsze niż wcześniej: 

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
   USING OLD;

   RETURN OLD;
END
$func$  LANGUAGE plpgsql;

SQL Fiddle.
Współpracuje z VALUES wyraz twarzy również.

Główne punkty

  • użyj format () lub quote_ident() aby w razie potrzeby cytować identyfikatory i bronić się przed SQL injection.
    To jest konieczne , nawet z własnym stołem nazwiska!
  • schemat-kwalifikuje nazwę tabeli. W zależności od prądu search_path Ustawienie nagiej nazwy tabeli może w przeciwnym razie zostać rozdzielone na inną tabelę o tej samej nazwie w innym schemacie.
  • użycie EXECUTE dla dynamicznych instrukcji DDL.
  • podaj wartości bezpiecznie z USING klauzula.
  • zapoznaj się z instrukcją fine na temat wykonywania dynamicznych poleceń w plpgsql.
  • zauważ, że RETURN OLD; w funkcji wyzwalacza jest wymagana dla trigger BEFORE DELETE. szczegóły w instrukcji tutaj.

Otrzymujesz komunikat o błędzie w swojej prawie udanej wersji, ponieważ OLD jest niewidoczny wewnątrz EXECUTE. Jeśli chcesz połączyć poszczególne wartości dekomponowanego wiersza, tak jak próbowałeś, musisz przygotować tekstową reprezentację każdej kolumny za pomocą quote_literal(), aby zagwarantować poprawną składnię. Musisz również znać nazwy kolumn przed ich obsługą lub zapytaniem katalogi systemowe-co jest sprzeczne z ideą posiadania prostej, dynamicznej funkcji wyzwalania ...

Moje rozwiązanie pozwala uniknąć tych komplikacji. Również nieco uproszczone.

PostgreSQL 9.0 lub wcześniejszy

format() nie jest jeszcze dostępny, więc: 

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger AS
$func$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;
END
$func$  LANGUAGE plpgsql;

Powiązane:

 47
Author: Erwin Brandstetter,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-04 13:43:13

Natknąłem się na to, bo szukałem dynamicznego wyzwalacza. W ramach podziękowania za pytanie i odpowiedzi zamieszczę moje rozwiązanie dla Postgres 9.3.

CREATE OR REPLACE FUNCTION set_deleted_instead_of_delete()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format('UPDATE %I set deleted = now() WHERE id = $1.id', TG_TABLE_NAME)
    USING OLD;
    RETURN NULL;
END;
$$ language plpgsql;
 1
Author: robkorv,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-22 19:24:55