Thread: 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:

> 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?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

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