Re: Resolution for "ERROR: cannot handle whole-row reference" ?

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> CREATE FUNCTION f1(RECORD) RETURNS BOOL LANGUAGE 'plpgsql' AS 'BEGIN
> RETURN TRUE; END;';

You can't use RECORD as the declaration of a plpgsql function parameter,
only as its result type. (Although I wonder whether we couldn't treat
it as a sort of anyarray-like pseudo-type ... but that's for the future.)

What you want is to declare the function as taking the table rowtype:

regression=# create table t1(f1 int, f2 text, f3 int);
CREATE TABLE
regression=# insert into t1 values(42,'z',44);
INSERT 1259013 1
regression=# create function foo(t1) returns int as '
regression'# begin
regression'# return $1.f1;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo(t1.*) from t1;
foo
-----
42
(1 row)

BTW, if you try

regression=# create function f1(t1) returns int as '
regression'# begin
regression'# return $1.f1;
regression'# end' language plpgsql;
ERROR: "f1" is already an attribute of type t1

The reason for this is a historical behavior inherited from PostQUEL:
a function invocation like "f1(t1.*)" can also be written "t1.f1", so
the function name can't conflict with any column name of the table.
However this only applies to single-argument functions, so if you have
other things to pass in besides the row, there's no problem.

> CREATE RULE t1_ins AS ON INSERT TO t1 DO INSTEAD SELECT f1(NEW);
> ERROR: function f1(t1) does not exist

I am not sure any of this works for NEW or OLD references in rules,
though, because they're not really tables.

regards, tom lane