Re: Documentation regarding %ROWTYPE in PL/PgSQL

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?