PL/pgSQL seems tantalizingly close to being useful for developing a
generalized trigger function for notifying the client of changes. I
don't know whether I'm missing something or whether we're missing a
potentially useful feature here. Does anyone see how to fill in
where the commented question is, or do I need to write this function
in C?
Alternatively, I guess, I could write a C-based
quote_literal(record, int2) and/or quote_literal(record, name)
function to use there.
create or replace function tcn_notify() returns trigger
language plpgsql as $tcn_notify$
declare
keycols int2vector;
keycolname text;
channel text;
payload text;
begin
select indkey from pg_catalog.pg_index
where indrelid = tg_relid and indisprimary
into keycols;
if not found then
raise exception 'no primary key found for table %.%',
quote_ident(tg_table_schema), quote_ident(tg_table_name);
end if;
channel := 'tcn' || pg_backend_pid()::text;
payload := quote_ident(tg_table_name) || ','
|| substring(tg_op, 1, 1);
for i in array_lower(keycols, 1)..array_upper(keycols, 1) loop
select quote_ident(attname) from pg_catalog.pg_attribute
where attrelid = tg_relid and attnum = keycols[i]::oid
into keycolname;
payload := payload || ',' || keycolname || '=';
-- How do I append the quote_literal(value) ?????
end loop;
perform pg_notify(channel, payload);
return null; -- ignored because this is an AFTER trigger
end;
$tcn_notify$;
It would surprise me if nobody else has wanted to do something like
this. The only reason we hadn't hit it yet is that we'd been
striving for portable code and had been doing such things in a Java
tier outside the database.
-Kevin
