Re: Documentation regarding %ROWTYPE in PL/PgSQL

I recently referred to the manual (section 23.3) to work out how to
write a PL/PgSQL function that accepted a row as a parameter.

Reading between a few lines I got the impression that the manual
suggested something like:

CREATE or REPLACE myfunc( tablename%ROWTYPE ) RETURNS ...

When I finally got my function working, I found I had:

CREATE or REPLACE myfunc( tablename ) RETURNS ...

This is brilliant :-), and in fact the manual foreshadows it:

"although one might expect a bare table name to work as a type
declaration, it won't be accepted within
<application>PL/pgSQL</application> functions."

So I guess the manual needs a little update there, or perhaps the syntax
that I used only works in the variable list, so I was lucky.

I would happily supply a patch to the documentation myself, except that
I don't really know what the correct answer is! The docs get a bit hazy
in this area regarding the differences between function parameters,
declared variables and declared aliases.

If someone wants to supply me with the lowdown (or tell me who they are
and I can quiz them :-) then I will produce a patch for this area.

I'm doing this under 7.2.1, but I've checked CVS as far as I am able and
this appears not to have changed.

Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?

Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz> writes:
> Reading between a few lines I got the impression that the manual
> suggested something like:
> CREATE or REPLACE myfunc( tablename%ROWTYPE ) RETURNS ...
> When I finally got my function working, I found I had:
> CREATE or REPLACE myfunc( tablename ) RETURNS ...
> This is brilliant :-), and in fact the manual foreshadows it:
> "although one might expect a bare table name to work as a type
> declaration, it won't be accepted within
> <application>PL/pgSQL</application> functions."

IMHO, %ROWTYPE is an Oracle-ism that we support in plpgsql functions
for compatibility's sake. It should work to just use the name of the
composite type (= name of the table). But there's at least one place
where plpgsql currently requires the %ROWTYPE marker, though I forget
the details.

The variant that is supported in CREATE FUNCTION argument and result
declarations (outside the function body) is "tablename%TYPE" and
"tablename.fieldname%TYPE". I have no idea how compatible that is
with Oracle, though I believe it was suggested by someone who wanted
to port Oracle code.

> I would happily supply a patch to the documentation myself, except that
> I don't really know what the correct answer is! The docs get a bit hazy
> in this area regarding the differences between function parameters,
> declared variables and declared aliases.

I'm not sure either. A little experimentation seems called for.

regards, tom lane

On Tue, 2002-05-28 at 04:59, Tom Lane wrote:
> Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz> writes:
> > Reading between a few lines I got the impression that the manual
> > suggested something like:
> > CREATE or REPLACE myfunc( tablename%ROWTYPE ) RETURNS ...
> > When I finally got my function working, I found I had:
> > CREATE or REPLACE myfunc( tablename ) RETURNS ...
> > This is brilliant :-), and in fact the manual foreshadows it:
> > "although one might expect a bare table name to work as a type
> > declaration, it won't be accepted within
> > <application>PL/pgSQL</application> functions."
>
> IMHO, %ROWTYPE is an Oracle-ism that we support in plpgsql functions
> for compatibility's sake. It should work to just use the name of the
> composite type (= name of the table). But there's at least one place
> where plpgsql currently requires the %ROWTYPE marker, though I forget
> the details.

Well, based on what you say here, and my passing encounters with Oracle
in the past, I can contrive an example function which seems to hit all
of the points necessary to show someone a way to use these things:

CREATE OR REPLACE FUNCTION
plpgsql_t1( constituents, INT4 ) RETURNS TEXT AS '
DECLARE
c ALIAS FOR $1;
default_centre ALIAS FOR $2;
addressee TEXT;
cc centres%ROWTYPE;
fullname constituents.pr_first_name%TYPE;
BEGIN

SELECT * INTO cc FROM centres
WHERE centres.centre_id = c.primary_centre_id;

IF NOT FOUND THEN
SELECT * INTO cc FROM centres
WHERE centres.centre_id = default_centre;
END IF;
fullname = c.pr_first_name || '' '' || c.pr_last_name ;

RETURN fullname || '', '' || cc.centre_name;

END;
' LANGUAGE plpgsql ;

This works fine against 7.2.1 :

pcno=# select plpgsql_t1(constituents, 5) from constituents limit 7;
plpgsql_t1
------------------------------------------------------
Hayley Campbell, Whangarei Parents Centre
Erin Smith, Wellington South Parents Centre
Rachel Dawson, Tauranga Parents Centre
Jacquelyn Satherley, Palmerston North Parents Centre
Natalie Tankersley, Palmerston North Parents Centre
Joy Tavinor, Whangarei Parents Centre
Nicola Gee, Wellington South Parents Centre
(7 rows)

So unless anyone has anything to add I will rustle up an appropriate
patch for the docs that tries to make all this a bit clearer.

> The variant that is supported in CREATE FUNCTION argument and result
> declarations (outside the function body) is "tablename%TYPE" and
> "tablename.fieldname%TYPE". I have no idea how compatible that is
> with Oracle, though I believe it was suggested by someone who wanted
> to port Oracle code.

Yes, I seem to recall that is compatible with Oracle except I am not
quite so sure about 'tablename%TYPE' - perhaps some Oracle PL/SQL guru
can confirm or deny. In the above example if I change:
cc centres%ROWTYPE;
to either:
cc centres%TYPE;
cc centres;

I get errors. Likewise I appear to have to supply %TYPE to the field,
and I can't supply either %ROWTYPE or %TYPE within the parameter
definition without an error either.

In other words there may be a variant of the above that works, but I
haven't been able to find it.

>
> > I would happily supply a patch to the documentation myself, except that
> > I don't really know what the correct answer is! The docs get a bit hazy
> > in this area regarding the differences between function parameters,
> > declared variables and declared aliases.
>
> I'm not sure either. A little experimentation seems called for.

Experimentation done - now for a patch.

Is it a good idea to provide an example (such as the above), or should I
just try and describe the behaviour?

Thanks,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?

Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz> writes:
> ... I can't supply either %ROWTYPE or %TYPE within the parameter
> definition without an error either.

Looks like I was mistaken: table.field%type works, but table%type
does not:

test72=# create table foo (f1 int, f2 text);
CREATE
test72=# create function foo(foo.f1%type) returns int as 'select $1'
test72-# language sql;
NOTICE: foo.f1%TYPE converted to int4
CREATE
test72=# create function foo(foo%type) returns text as 'select $1.f2'
test72-# language sql;
ERROR: parser: parse error at or near "%"

So you must do it as:

test72=# create function foo(foo) returns text as 'select $1.f2'
test72-# language sql;
CREATE

> Is it a good idea to provide an example (such as the above), or should I
> just try and describe the behaviour?

Examples are generally good things ...

regards, tom lane

On Wed, 2002-05-29 at 01:41, Tom Lane wrote:
>
> > Is it a good idea to provide an example (such as the above), or should I
> > just try and describe the behaviour?
>
> Examples are generally good things ...

OK, the attached documentation patch provides some simple examples of
use of tablename as a parameter, %ROWTYPE and %TYPE.

In the end I decided that the documentation is literally correct, but
hard to follow without any examples explicitly showing the use of a
table name as a parameter.

Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Andrew McMillan wrote:
> On Wed, 2002-05-29 at 01:41, Tom Lane wrote:
> >
> > > Is it a good idea to provide an example (such as the above), or should I
> > > just try and describe the behaviour?
> >
> > Examples are generally good things ...
>
> OK, the attached documentation patch provides some simple examples of
> use of tablename as a parameter, %ROWTYPE and %TYPE.
>
> In the end I decided that the documentation is literally correct, but
> hard to follow without any examples explicitly showing the use of a
> table name as a parameter.
>
> Cheers,
> Andrew.
> --
> --------------------------------------------------------------------
> Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
> WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
> DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
> Are you enrolled at http://schoolreunions.co.nz/ yet?

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Patch applied. Thanks.

---------------------------------------------------------------------------

Andrew McMillan wrote:
> On Wed, 2002-05-29 at 01:41, Tom Lane wrote:
> >
> > > Is it a good idea to provide an example (such as the above), or should I
> > > just try and describe the behaviour?
> >
> > Examples are generally good things ...
>
> OK, the attached documentation patch provides some simple examples of
> use of tablename as a parameter, %ROWTYPE and %TYPE.
>
> In the end I decided that the documentation is literally correct, but
> hard to follow without any examples explicitly showing the use of a
> table name as a parameter.
>
> Cheers,
> Andrew.
> --
> --------------------------------------------------------------------
> Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
> WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
> DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
> Are you enrolled at http://schoolreunions.co.nz/ yet?

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026