473,327 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 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 4917
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: t_pet422 | last post by:
Hi, I've been scouring the net and reading the PostgreSQL docs for a while now trying to learn how to create a recursive function in PL/pgSQL that will return a whole subtree given a starting...
1
by: Clive Page | last post by:
What I'd like to do is use dblink to extract a few rows from a remote database and manipulate these within a function in pl/pgsql. Something like this: CREATE OR REPLACE FUNCTION find() RETURNS...
4
by: Rajesh Kumar Mallah | last post by:
Hi, We need to implement following logic efficiently. SELECT * from some_table where .... IF rows_matched = 1 THEN use the single row that matched.
1
by: D. Dante Lorenso | last post by:
I just wrote a PL/PGSQL function that is working, but I don't know why it is... I have a foreign key constraint defined on: transaction.invoice_id --> invoice.invoice_id But I did NOT state...
3
by: ezra epstein | last post by:
I'm been banging my head over this for a little while now. Here's a simple function to return a record: <code> CREATE OR REPLACE FUNCTION create_item_record_for_override(INTEGER, VARCHAR )...
1
by: Graeme Hinchliffe | last post by:
Hiya, Not had much experience with tiggers under postgres but am liking them so far. My problem is this. I am writing an updates system, postgres holds the master copy of the database, any...
0
by: Google Mike | last post by:
I had RH9 Linux. It came with pgSQL, but I couldn't seem to figure out how to get PL/pgSQL going. I read the HTML documentation that came with it and was confused until I tried a few different...
2
by: Tim Vadnais | last post by:
Hi, My boss wants to add some logging functionality to some of our tables on update/delete/insert. I need to log who, when, table_name, field name, original value and new value for each record,...
0
by: Matt | last post by:
I need an installation with Postgres support, and was happy to find in my phpinfo that there was a --with-pgsql=shared option in the config line. but I still got "call to undefined function:...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.