471,089 Members | 1,556 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

PL/pgsql - getting row from record

Hi,

I'm sure this has been asked before, but my searches haven't come up
with an example.

I'm writing a generic trigger function called from different tables in
plpgsql that needs to get the value of the primary key column from OLD.

I can find out the pkey column name by querying the system tables, but
the only way I've found of getting the value of that column is by
creating a temporary table and using FOR ... EXECUTE to get the value
back out. My code for doing that is below, but it seems mighty ugly.

So, two questions:
1. Is there a better way of getting arbitrary columns from a record?
2. If not, will I run into problems with the name of the temporary table
if two processes call this function at the same time, or are temp names
unique to the particular session?

CREATE TEMPORARY TABLE get_colval_from_record_tmp
AS SELECT OLD.*;
FOR srow IN EXECUTE
''SELECT '' || col || '' AS scol
FROM get_colval_from_record_tmp''
LOOP
colval := srow.scol;
END LOOP;
DROP TABLE get_colval_from_record_tmp;

Thanks for the help,

Matt

BTW: I noticed on http://developer.postgresql.org/todo.php there's an
item "Allow PL/pgSQL to name columns by ordinal position, e.g. rec.(3)".
That'd do me fine, but I guess I'd better ask on the dev list as to when
this might happen :)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
2 4844
Matt wrote:
So, two questions:
1. Is there a better way of getting arbitrary columns from a record?
Not really.
2. If not, will I run into problems with the name of the temporary table
if two processes call this function at the same time, or are temp names
unique to the particular session?


Temporary tables are session-specific.

Really, you'd be better off using one of the more interpreted languages
for this specific task though.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2
Thanks for the quick response!
Really, you'd be better off using one of the more interpreted languages
for this specific task though.


Was afraid someone would say that. Time to polish up those tarnished
perl skills ;)

Matt
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by t_pet422 | last post: by
4 posts views Thread by Rajesh Kumar Mallah | last post: by
1 post views Thread by Graeme Hinchliffe | last post: by
reply views Thread by Google Mike | last post: by
2 posts views Thread by Tim Vadnais | last post: by
reply views Thread by Matt | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.