Given the following schema:
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;
I try to create a generic trigger function in plpgsql, which enables updating the view:
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();
An unsuccessful effort of mine was:
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;
The problem is the update
statement. I am unable to come up with a syntax that would work here. I have successfully implemented this in PL/Perl, but would be interested in a plpgsql-only solution.
Any ideas?
Update
As @Erwin Brandstetter suggested, here is the code for my PL/Perl solution. I incoporated some of his suggestions.
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;
It is very easy to update multiple columns in PostgreSQL. Here is the syntax to update multiple columns in PostgreSQL. UPDATE table_name SET column1 = value1, column2 = value2, ... [WHERE condition];
Triggers on Events. PL/pgSQL can be used to define trigger functions on data changes or database events. A trigger function is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger (for data change triggers) or event_trigger (for database event triggers).
Syntax. CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- Trigger logic goes here.... ]; Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.
While @Gary's answer is technically correct, he fails to mention that PostgreSQL does support this form:
UPDATE tbl
SET (col1, col2, ...) = (expression1, expression2, ..)
Read the manual on UPDATE
once more.
It's still tricky to get his done with dynamic SQL. Since you didn't specify, I am assuming a simple case where views consist of the same columns as their underlying tables.
CREATE VIEW tbl_view AS SELECT * FROM tbl;
The special record NEW
is not visible inside EXECUTE
. I pass NEW
as a single parameter with the USING
clause of EXECUTE
.
As discussed, UPDATE
with list-form needs individual values. I use a subselect to split the record into individual columns:
UPDATE ...
FROM (SELECT ($1).*) x
(Parenthesis around $1
are not optional.) This allows me to simply use two column lists built with string_agg()
from the catalog table: one with and one without table qualification.
It's not possible to assign a row value as a whole to individual columns. The manual:
According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. PostgreSQL only allows the source value to be a row constructor or a sub-
SELECT
.
INSERT
is implemented simpler. Assuming the structure of view and table are identical I omit the column definition list. (Can be improved, see below.)
I made a number of updates to your approach to make it shine.
Trigger function for 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;
Trigger function for 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;
Triggers:
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 demonstrating INSERT
and UPDATE
.
Include the schema name to make the table reference unambiguous. There can be multiple instances of the same table name in the same database in multiple schemas!
Query pg_attribute
instead of information_schema.columns
. That's less portable, but much faster and allows me to use the table-OID.
Table names are NOT safe against SQLi when handled as strings like in building queries for dynamic SQL. Escape with quote_ident()
or format()
or with an object-identifer type. This includes the special trigger function variables TG_TABLE_SCHEMA
and TG_TABLE_NAME
!
Cast to the object identifier type regclass
to assert the table name is valid and get the OID for the catalog look-up.
Optionally use format()
to build the dynamic query string safely.
No need for dynamic SQL for the first query on the catalog tables. Faster, simpler.
Use RETURN NEW
instead of RETURN NULL
in these trigger functions unless you know what you are doing. (NULL
would cancel the INSERT
for the current row.)
This simple version assumes that every table (and view) has a unique column named id
. A more sophisticated version might use the primary key dynamically.
The function for UPDATE
allows the columns of view and table to be in any order, as long as the set is the same.
The function for INSERT
expects the columns of view and table to be in identical order. If you want to allow arbitrary order, add a column definition list to the INSERT
command, just like with UPDATE
.
Updated version also covers changes to the id
column by using OLD
additionally.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With