postgresql - INSERT con el nombre de la tabla dinámica en la función de activación

triggers dynamic-sql

Question

No estoy seguro de cómo lograr algo como lo siguiente:

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;

Es decir, insertar valores en una tabla con un nombre generado dinámicamente.
Ejecutando el código anterior produce:

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

Parece sugerir que las variables no se expanden / permiten como nombres de tablas. No he encontrado ninguna referencia a esto en el manual de Postgres.

Ya he experimentado con EXECUTE así:

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

Pero sin suerte:

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

El tipo RECORD parece estar perdido: OLD.* Parece haberse convertido en cadena y reparado, lo que genera todo tipo de problemas de tipo (por ejemplo, valores NULL ).

¿Algunas ideas?

Answer 1

PostgreSQL 9.1 o posterior

format() tiene una forma integrada de escaparse identificadores. Más simple que antes:

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.
Funciona con una expresión VALUES también.

Puntos principales

• Use format() o format() para citar identificadores cuando sea necesario y defenderse contra la inyección de SQL .
  Esto es necesario , ¡incluso con sus propios nombres de tabla!
• Esquema: califica el nombre de la tabla. Dependiendo de la configuración de la search_path actual , un nombre de tabla search_path podría resolverse en otra tabla del mismo nombre en un esquema diferente.
• Use EXECUTE para las sentencias DDL dinámicas.
• Pase los valores de forma segura con la cláusula USING .
• Consulte el fino manual sobre Ejecución de comandos dinámicos en plpgsql .
• Tenga en cuenta que RETURN OLD; en la función de disparo se requiere un disparador BEFORE DELETE . Detalles en el manual aquí.

Obtiene el mensaje de error en su versión casi exitosa porque OLD no está visible dentro de EXECUTE . Y si desea concatenar valores individuales de la fila descompuesta como lo intentó, debe preparar la representación de texto de cada columna con quote_literal() para garantizar una sintaxis válida. También debería saber los nombres de las columnas de antemano para manejarlos o consultar los catálogos del sistema, lo que va en contra de su idea de tener una función de activación dinámica simple ...

Mi solución evita todas estas complicaciones. También simplificado un poco.

PostgreSQL 9.0 o anterior

format() aún no está disponible, así que:

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;

Relacionado:

• ¿Cómo usar dinámicamente TG_TABLE_NAME en PostgreSQL 8.2?

Answer 2

Acabo de tropezar con esto porque estaba buscando un desencadenador dinámico INSTEAD OF DELETE . Como agradecimiento por la pregunta y las respuestas, publicaré mi solución para 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;