Plpgsql problem passing ROWTYPE to function - Mailing list pgsql-general

From Karl O. Pinc
Subject Plpgsql problem passing ROWTYPE to function
Date
Msg-id 20040429165601.A6347@mofo.meme.com
Whole thread Raw
Responses Re: Plpgsql problem passing ROWTYPE to function
List 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@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Arbitrary precision modulo operation
Next
From: "Gregory S. Williamson"
Date:
Subject: Re: "Idle in Transaction" and hung connections