Skip site navigation (1) Skip section navigation (2)

Re: Documentation regarding %ROWTYPE in PL/PgSQL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Documentation regarding %ROWTYPE in PL/PgSQL
Date: 2002-05-27 16:59:25
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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

In response to


pgsql-bugs by date

Next:From: pgsql-bugsDate: 2002-05-27 17:51:58
Subject: Bug #680: NOCREATETABLE
Previous:From: andrea gelminiDate: 2002-05-27 16:21:28
Subject: problem with date cast

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group