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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sean Chittenden <sean(at)chittenden(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Resolution for "ERROR: cannot handle whole-row reference" ?
Date: 2004-03-28 16:00:57
Message-ID: 15965.1080489657@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: David GaramondDate: 2004-03-28 18:45:18
Subject: Re: Index usage for BYTEA column in OR/IN clause
Previous:From: Frank FinnerDate: 2004-03-28 15:55:05
Subject: Re: win32 users list (Re: Native Win32 port - PLEASE!)

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