Andrew McMillan <andrew@catalyst.net.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