update - Actualizar varias columnas en una función de activación en plpgsql

update por lotes sql server

Question

Dado el siguiente esquema:

create table account_type_a (
  id SERIAL UNIQUE PRIMARY KEY,
  some_column VARCHAR
);

create table account_type_b (
  id SERIAL UNIQUE PRIMARY KEY,
  some_other_column VARCHAR
);

create view account_type_a view AS select * from account_type_a;
create view account_type_b view AS select * from account_type_b;

Intento crear una función desencadenante genérica en plpgsql, que permite actualizar la vista:

create trigger trUpdate instead of UPDATE on account_view_type_a
for each row execute procedure updateAccount();    
create trigger trUpdate instead of UPDATE on account_view_type_a
for each row execute procedure updateAccount();

Un esfuerzo mío no exitoso fue:

create function updateAccount() returns trigger as $$
declare
  target_table varchar := substring(TG_TABLE_NAME from ''(.+)_view'');
  cols varchar;
begin
  execute ''select string_agg(column_name,$1) from information_schema.columns
           where table_name = $2'' using '','', target_table into cols;
  execute ''update '' || target_table || '' set ('' || cols || '') =  select ($1).*
           where id = ($1).id'' using NEW;
  return NULL;
end;
$$ language plpgsql;

El problema es la declaración de update . No puedo encontrar una sintaxis que funcione aquí. Lo he implementado con éxito en PL / Perl, pero estaría interesado en una solución solo de plpgsql.
¿Algunas ideas?

Actualizar

Como sugirió @Erwin Brandstetter, aquí está el código para mi solución PL / Perl. Incoporated algunas de sus sugerencias.

create function f_tr_up() returns trigger as $$
  use strict;
  use warnings;
  my $target_table = quote_ident($_TD->{''table_name''}) =~ s/^([/w]+)_view$/$1/r;
  my $NEW = $_TD->{''new''};
  my $cols = join('','', map { quote_ident($_) } keys $NEW);
  my $vals = join('','', map { quote_literal($_) } values $NEW);
  my $query = sprintf(
    "update %s set (%s) = (%s) where id = %d",
    $target_table,
    $cols,
    $vals,
    $NEW->{''id''});
  spi_exec_query($query);
return;
$$ language plperl;

Answer 1

Postgresql no admite la actualización de múltiples columnas mediante el set (col1,col2) = select val1,val2 sintaxis.

Para lograr lo mismo en postgresql usarías

update target_table
set col1 = d.val1,
    col2 = d.val2
from source_table d
where d.id = target_table.id

Esto va a hacer que la consulta dinámica sea un poco más compleja de construir, ya que tendrá que repetir la lista de nombres de columnas que está utilizando en campos individuales. Te sugiero que uses array_agg lugar de string_agg ya que una matriz es más fácil de procesar que dividir la cadena nuevamente.

Postgresql UPDATE sintaxis

documentación sobre la función array_agg

Answer 2

Si bien la respuesta de @ Gary es técnicamente correcta, no menciona que PostgreSQL admite esta forma:

UPDATE tbl
SET (col1, col2, ...) = (expression1, expression2, ..)

Lea el manual sobre UPDATE una vez más.

Todavía es complicado hacer su trabajo con SQL dinámico. Como no especificó, asumo un caso simple en el que las vistas constan de las mismas columnas que sus tablas subyacentes.

CREATE VIEW tbl_view AS SELECT * FROM tbl;

Problemas

• El registro especial NEW no es visible dentro de EXECUTE . Paso NEW como un parámetro único con la cláusula USING de EXECUTE .

• Como se discutió, UPDATE con list-form necesita valores individuales. Utilizo una subselección para dividir el registro en columnas individuales:

  UPDATE ...
  FROM  (SELECT ($1).*) x

  (Los paréntesis de alrededor de $1 no son opcionales). Esto me permite simplemente usar dos listas de columnas string_agg() con string_agg() de la tabla de catálogo: una con y sin calificación de tabla.

• INSERT se implementa más simple. Asumiendo que la estructura de la vista y la tabla son idénticas, omito la lista de definición de columna. (Se puede mejorar, ver a continuación).

Solución

Hice una serie de actualizaciones a su enfoque para que brille.

Función de activación para UPDATE :

CREATE OR REPLACE FUNCTION f_trg_up()
  RETURNS TRIGGER AS
$func$
DECLARE
   tbl  text := quote_ident(TG_TABLE_SCHEMA) || ''.''
             || quote_ident(substring(TG_TABLE_NAME from ''(.+)_view$''));
   cols text;
   vals text;
BEGIN
   SELECT INTO cols, vals
          string_agg(quote_ident(attname), '', '')
         ,string_agg(''x.'' || quote_ident(attname), '', '')
   FROM   pg_attribute
   WHERE  attrelid = tbl::regclass
   AND    NOT attisdropped   -- no dropped (dead) columns
   AND    attnum > 0;        -- no system columns

   EXECUTE format(''
   UPDATE %s t
   SET   (%s) = (%s)
   FROM  (SELECT ($1).*) x
   WHERE  t.id = ($2).id''
   , tbl, cols, vals) -- assuming unique "id" in every table
   USING NEW, OLD;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Función de disparo para INSERT :

CREATE OR REPLACE FUNCTION f_trg_ins()
  RETURNS TRIGGER AS
$func$
DECLARE
    tbl text := quote_ident(TG_TABLE_SCHEMA) || ''.''
             || quote_ident(substring(TG_TABLE_NAME from ''(.+)_view$''));
BEGIN
   EXECUTE ''INSERT INTO '' || tbl || '' SELECT ($1).*''
   USING NEW;

   RETURN NEW;  -- don''t return NULL unless you know what you''re doing
END
$func$ LANGUAGE plpgsql;

Disparadores:

CREATE TRIGGER trg_instead_up
INSTEAD OF UPDATE ON a_view
FOR EACH ROW EXECUTE PROCEDURE f_trg_up();

CREATE TRIGGER trg_instead_ins
INSTEAD OF INSERT ON a_view
FOR EACH ROW EXECUTE PROCEDURE f_trg_ins();

SQL Fiddle demostrando INSERT y UPDATE .

Puntos principales

• Incluya el nombre del esquema para que la referencia de la tabla no sea ambigua. ¡Puede haber múltiples instancias del mismo nombre de tabla en la misma base de datos en múltiples esquemas!

• Consulta pg_attribute lugar de information_schema.columns . Eso es menos portátil, pero mucho más rápido y me permite usar la tabla OID.

  • Cómo verificar si una tabla existe en un esquema dado

• Los nombres de tabla NO son seguros contra SQLi cuando se manejan como cadenas, como en la creación de consultas para SQL dinámico. Escape con quote_ident() o format() o con un tipo de identificador de objeto . Esto incluye las variables especiales de la función de disparo TG_TABLE_SCHEMA y TG_TABLE_NAME .

• Transmitir al identificador de objeto tipo regclass para afirmar que el nombre de la tabla es válido y obtener el OID para la búsqueda del catálogo.

• Opcionalmente use format() para construir la cadena de consulta dinámica de forma segura.

• No hay necesidad de SQL dinámico para la primera consulta en las tablas de catálogo. Más rápido, más simple.

• Use RETURN NEW lugar de RETURN NULL en estas funciones de activación a menos que sepa lo que está haciendo. ( NULL cancelaría el INSERT para la fila actual.)

• Esta versión simple supone que cada tabla (y vista) tiene una columna única llamada id . Una versión más sofisticada podría usar la clave principal de forma dinámica.

• La función de UPDATE permite que las columnas de vista y tabla estén en cualquier orden , siempre que el conjunto sea el mismo. La función de INSERT espera que las columnas de vista y la tabla estén en el mismo orden . Si desea permitir un orden arbitrario, agregue una lista de definición de columna al comando INSERT , al igual que con UPDATE .

• La versión actualizada también cubre los cambios en la columna de id al usar OLD adicionalmente.