Plpgsql problem passing ROWTYPE to function

From: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Plpgsql problem passing ROWTYPE to function
Date: 2004-04-29 21:56:01
Message-ID: 20040429165601.A6347@mofo.meme.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Thought perhaps some other eyes than mine can tell if I'm doing
something wrong here or if there's a bug somewhere. I've never
passed a ROWTYPE varaible to a function but I don't see where
the problem is.

I keep getting errors like (the first is my debug output):
NOTICE: last cycle is: 11
WARNING: Error occurred while executing PL/pgSQL function
rebuild_cyclestats
WARNING: line 69 at assignment
ERROR: Attribute "last_cycle" not found

(Line number is off as I've snipped out code to post here.)

$ psql --version
psql (PostgreSQL) 7.3.4
$ rpm -q postgresql
postgresql-7.3.4-3.rhl9
$ cat /etc/redhat-release Red Hat Linux release 9 (Shrike)

(I don't know how to query the server for it's version number,
I assume it's in sync with the client.)
-------------------------------------------------

CREATE FUNCTION rebuild_cyclestats(cycles.sname%TYPE)
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
AS '

DECLARE
this_sname ALIAS for $1;

last_cycle cycles%ROWTYPE;
this_cycle cycles%ROWTYPE;
this_statdate biograph.statdate%TYPE;

BEGIN

SELECT INTO last_cycle * FROM cycles WHERE sname = this_sname
AND seq = 1;
IF NOT FOUND THEN
-- There are no cycles. Do nothing.
RETURN 0;
END IF;

-- debug
raise notice ''last cycle is: %'', last_cycle.cid;

-- The female''s last cycle continues to her statdate.
SELECT INTO this_statdate statdate FROM biograph WHERE sname =
this_sname;
--error is here
PERFORM _makestats(this_sname
, last_cycle
, NULL
, this_statdate);

RETURN 0;
END;
';

CREATE FUNCTION _makestats(cycles.sname%TYPE
, cycles
, cycles.sname%TYPE
, cycles.tdate%TYPE)
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
AS ' ...

------------------------------------------------------------------
BTW, if I write:

CREATE FUNCTION _makestats(cycles.sname%TYPE
, cycles%ROWTYPE
, cycles.sname%TYPE
, cycles.tdate%TYPE)

I always get:
ERROR: parser: parse error at or near "%" at character 81

Thanks.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-04-29 22:03:27 Re: "Idle in Transaction" and hung connections
Previous Message Dann Corbit 2004-04-29 21:35:46 Re: Arbitrary precision modulo operation