accessing fully qualified fields in records in PLPGSQL?
How does one access a field in a record from a query like so:
FOR REC IN SELECT myTable.oid FROM myTable, yourTable WHERE (myTable.id =
yourTable.id) LOOP
VAL := REC.oid;
VAL := REC.myTable.oid;
END LOOP;
I get errors in PLPGSQL compilation for both the assignment statements
above. What is the correct way to get the myTable oid in the REC record?
Wade Oberpriller http://www.storagetek.com
Software Development Phone: (763) 424-1538
StorageTek: MRDC (800) 328-9108 ext. 1538
wade_oberpriller@storagetek.com Fax: (763) 391-1095
dev=> CREATE FUNCTION test() RETURNS INTEGER AS '
dev'> DECLARE
dev'> rec RECORD;
dev'> val INTEGER;
dev'> BEGIN
dev'> FOR rec IN SELECT oid FROM inv_locations LOOP
dev'> val := rec.oid;
dev'> END LOOP;
dev'> RETURN val;
dev'> END;' LANGUAGE 'plpgsql';
CREATE
dev=> select test();
test
--------
534321
(1 row)
Did not see your declare etc but this one works. Returns the final oid for
that table
HTH
Darren Ferguson
On Mon, 25 Mar 2002, Oberpriller, Wade D. wrote:
Show quoted text
How does one access a field in a record from a query like so:
FOR REC IN SELECT myTable.oid FROM myTable, yourTable WHERE (myTable.id =
yourTable.id) LOOP
VAL := REC.oid;
VAL := REC.myTable.oid;
END LOOP;I get errors in PLPGSQL compilation for both the assignment statements
above. What is the correct way to get the myTable oid in the REC record?Wade Oberpriller http://www.storagetek.com
Software Development Phone: (763) 424-1538
StorageTek: MRDC (800) 328-9108 ext. 1538
wade_oberpriller@storagetek.com Fax: (763) 391-1095---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
I'm tracking development by storing pg_dumps in CVS. This is pretty handy.
I even have a script that makes diffs of the versions to help us write
upgrade patches. If anyone has hints or wants build a general solution for
this type of thing, I'm interested.
The problem is this:
We create two tables, each with implicit SEQUENCES by using the SERIAL datatype.
Then:
pg_dump --schema --table=sometable databasename
pg_dump --schema --table=anothertable databasename
The output for sometable includes the "CREATE SEQUENCE
sometable_column_seq" statment, but the output for anothertable does not.
It only referes to that sequence in the column type nextval statement.
How can I reliably pg_dump the sequence with the table?
Mike Adler
Oberpriller, Wade D. wrote:
How does one access a field in a record from a query like so:
FOR REC IN SELECT myTable.oid FROM myTable, yourTable WHERE (myTable.id =
yourTable.id) LOOP
VAL := REC.oid;
VAL := REC.myTable.oid;
END LOOP;I get errors in PLPGSQL compilation for both the assignment statements
above. What is the correct way to get the myTable oid in the REC record?
REC beeing of type record and VAL beeing of type oid in the
declare section, I don't get any error on the REC.oid
attempt. What version of PostgreSQL are you using (this is
v7.2).
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com