Passing a ROWTYPE to a function

Started by Eric Brownover 21 years ago3 messagesgeneral
Jump to latest
#1Eric Brown
eric.brown@propel.com

I'm trying to write a function that takes a %ROWTYPE as an argument.
I'm just not sure how to call it from another function.
This is what I tried:
CREATE TABLE t1 (x int, y int);
INSERT INTO t1 VALUES (1, 2);
CREATE OR REPLACE FUNCTION g1(t1) RETURNS int LANGUAGE plpgsql AS '
BEGIN
RETURN $1.y;
END';

CREATE OR REPLACE FUNCTION g2(int) RETURNS int LANGUAGE plpgsql AS '
DECLARE item t1%ROWTYPE;
BEGIN
SELECT INTO item * FROM t1 WHERE x = $1;
RETURN g1(item);
END';

SELECT g2(1);

This is what I got:
CREATE TABLE
INSERT 28089 1
CREATE FUNCTION
CREATE FUNCTION
psql:/tmp/test.sql:16: ERROR: column "item" does not exist
CONTEXT: PL/pgSQL function "g2" line 4 at return

I'm using posgresql 7.4.6.

Thanks.

Eric Brown
408-571-6341
www.propel.com

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Eric Brown (#1)
Re: Passing a ROWTYPE to a function

Eric Brown <eric.brown@propel.com> writes:

CREATE OR REPLACE FUNCTION g2(int) RETURNS int LANGUAGE plpgsql AS '
DECLARE item t1%ROWTYPE;
BEGIN
SELECT INTO item * FROM t1 WHERE x = $1;
RETURN g1(item);
END';

This works in 8.0 but not earlier releases --- there was not support in
plpgsql for using whole-row variables in expressions.

regards, tom lane

#3Michael Fuhr
mike@fuhr.org
In reply to: Eric Brown (#1)
Re: Passing a ROWTYPE to a function

On Wed, Jan 05, 2005 at 05:04:03AM -0800, Eric Brown wrote:

I'm trying to write a function that takes a %ROWTYPE as an argument.

The code you posted works in 8.0. Here's an item from the 8.0
Release Notes:

* More support for composite types (row and record variables) in PL/pgSQL

For example, it now works to pass a rowtype variable to another
function as a single variable.

I don't think you could do this in versions prior to 8.0; see
previous discussion in the list archives. For example:

http://archives.postgresql.org/pgsql-general/2004-03/msg00862.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/