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