Re: How to extract a value from a record using attnum or attname?

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