Thread: Passing composite types to plpgsql functions

Passing composite types to plpgsql functions

From
"Jeff Waugh"
Date:
/doc/html/plpgsql-description.html states that:

"Parameters to a function can be composite types (complete table rows). In
that case, the corresponding identifier $n will be a rowtype, but it must be
aliased using the ALIAS command described above."

What syntax do I need in my function declaration to receive a parameter of
something%ROWTYPE ?

Thanks,
-Jeff



Re: Passing composite types to plpgsql functions

From
Tom Lane
Date:
"Jeff Waugh" <jaw@ic.net> writes:
> /doc/html/plpgsql-description.html states that:
> "Parameters to a function can be composite types (complete table rows). In
> that case, the corresponding identifier $n will be a rowtype, but it must be
> aliased using the ALIAS command described above."

That's out of date as of 7.1 --- you can alias a rowtype parameter,
but you don't have to.

> What syntax do I need in my function declaration to receive a parameter of
> something%ROWTYPE ?

You use the table name as a datatype.  Trivial example:

regression=# create table foo(f1 int, f2 int);
CREATE
regression=# insert into foo values (1,2);
INSERT 723473 1
regression=# insert into foo values (7,11);
INSERT 723474 1
regression=# create function foosum(foo) returns int as
regression-# 'begin
regression'#   return $1.f1 + $1.f2;
regression'# end;' language 'plpgsql';
CREATE
regression=# select *,foosum(f) from foo f;
 f1 | f2 | foosum
----+----+--------
  1 |  2 |      3
  7 | 11 |     18
(2 rows)


            regards, tom lane