Thread: How to extract a value from a record using attnum or attname?
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
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
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > 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? See those: http://tapoueh.org/articles/blog/_Dynamic_Triggers_in_PLpgSQL.html http://www.pgsql.cz/index.php/PL_toolbox_%28en%29#Record.27s_functions > 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 Beware of attisdropped, which I've not fixed in the published URL before (the tapoueh.org one). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
[moving to -hackers with BC to -general] Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > >> 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? > > See those: > > http://tapoueh.org/articles/blog/_Dynamic_Triggers_in_PLpgSQL.html > http://www.pgsql.cz/index.php/PL_toolbox_%28en%29#Record.27s_functions > >> 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 > > Beware of attisdropped, which I've not fixed in the published URL > before (the tapoueh.org one). Thanks. In the absence of an earlier response, though, I went ahead and wrote the attached, which has passed some initial programmer testing and is scheduled to start business analyst testing tomorrow with the application software for production deployment in a couple months. We probably won't go back to PL/pgSQL for this now. I'm assuming that while I have an AccessShareLock on the index relation for the primary key, any attributes it tells me are used by that relation will not have the attisdropped flag set? What this trigger function does is to issue a NOTIFY to the channel specified as a parameter to the function in CREATE TRIGGER (with 'tcn' as the default), and a payload consisting of the table name, a code for the operation (Insert, Update, or Delete), and the primary key values. So, an update to a Party record for us might generate this NOTIFY payload: "Party",U,"countyNo"='71',"caseNo"='2011CF001234',"partyNo"='1' This is one of those things which our shop needs, but I was planning to post it for the first 9.2 CF fest to see if anyone else was interested. It struck me while typing this post that for general use the schema would probably need to be in there, but I'll worry about that later, if anyone else *is* interested. If anyone wants it I can provide Java code to tear apart the NOTIFY payloads using the Pattern and Matches classes. I'll add to the first 9.2 CF referencing this post. -Kevin
Attachment
I don't know if you can quite write the generalized notification function you want in plpgsql or not, but you can certainlywrite the meta-function that create the function for any table ;-) -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice