plpgsql and rowtypes

From: Dave Trombley <dtrom(at)bumba(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql and rowtypes
Date: 2002-01-01 23:31:08
Message-ID: 3C3246BC.5020401@bumba.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Happy New Year's day, and such!

Has anyone had any luck getting rowtyped declarations in plpgsql
to function? In particular, the documentation says:

23.3.2. Rowtypes

/name/ /table-datatype/;

A variable declared with a composite type (referenced by the name of the
table that defines that type) is called a / row/ variable. Such a
variable can hold a whole row of a SELECT or FOR query result, so long
as that query's column set matches the declared rowtype of the variable.
The individual fields of the row value are accessed using the usual dot
notation, for example rowvar.field.

Parameters to a function can be composite types (complete table rows).
In that case, the corresponding identifier $n will be a row variable,
and fields can be selected from it, for example $1.user_id .

Only the user-defined attributes of a table row are accessible in a
rowtype variable, not OID or other system attributes (because the row
could be from a view). The fields of the rowtype inherit the table's
field size or precision for data types such as char(n).

However, unless I am misunderstanding something silly, I get quite a
different result when I try this:

test=# create table testtable(a int, b int);
CREATE
test=# create function test() returns int as '
test'# DECLARE
test'# tt testtable;
test'# BEGIN
test'# tt.a := 4;
test'# RETURN tt.a;
test'# END;
test'# ' language 'plpgsql';
CREATE
test=# select test();
NOTICE: plpgsql: ERROR during compile of test near line 2
ERROR: parse error at or near "testtable"

Any ideas? I'm running 7.1.3...

Thanks,
dj trombley
<dtrom(at)bumba(dot)net>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Calvin Dodge 2002-01-01 23:37:17 Re: Filtering a bunch of records to one after a selection has returned a bunch of them
Previous Message Tom Lane 2002-01-01 23:23:25 Re: plpgsql and rowtypes