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

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-general(at)postgresql(dot)org>
Subject: How to extract a value from a record using attnum or attname?
Date: 2011-02-04 22:27:15
Message-ID: 4D4C28E3020000250003A411@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2011-02-04 22:53:15 Re: How to extract a value from a record using attnum or attname?
Previous Message Dmitriy Igrishin 2011-02-04 22:08:28 Re: Additional Grants To SuperUser?

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Kellerer 2011-02-04 22:53:15 Re: How to extract a value from a record using attnum or attname?
Previous Message Bruce Momjian 2011-02-04 21:53:17 Re: is_absolute_path incorrect on Windows