Kevin Grittner wrote on 04.02.2011 23:27:
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.
If you don't really need the key = value pairs, you can simply use: payload := payload || 'values: ' || ROW(old.*); this will append everything in one operation, but not in the col=value format Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
