Re: Passing RECORD variable from func1() to func2()

howzit Henry

I've pasted in the head of one of my functions. Hope it helps. It is
called through the following:

SELECT * from fn_usr32_show_sections ( <integer> );

Rory

On 07/09/04, Henry Combrinck (henry(at)metroweb(dot)co(dot)za) wrote:
> Thanks for the CREATE TYPE samples. *Using* the custom types seems to be
> a problem (or rather, I'm using it incorrectly). The following code fails
> with the error message
>
> "WARNING: Error occurred while executing PL/pgSQL function f_test00
> WARNING: line 8 at SQL statement
> ERROR: Attribute "rec1" not found"
>
> /* create same columns as table1 */
> create type TYPE_T1 as (col1 int, col2 int,... etc);
...
> declare
> rec1 TYPE_T1%ROWTYPE;
> begin
> for rec1 in select * from table1
> loop
> select f_test01(rec1); /* this is where it fails. */
> end loop;
> return 0;
> end;
> ' LANGUAGE 'plpgsql';

CREATE TYPE sec_sections as (
sectionid INTEGER,
sectionname VARCHAR,
secupdated VARCHAR,
secreports INTEGER,
secusers INTEGER
);

CREATE OR REPLACE FUNCTION
fn_usr32_show_sections ( integer ) RETURNS setof sec_sections
AS '
DECLARE
userid ALIAS for $1;
resulter sec_sections%rowtype;
BEGIN

IF userid is NULL THEN
RAISE EXCEPTION ''No user provided : ref usr32'';
RETURN 0;
END IF;

PERFORM fn_util07_isadmin(userid);

FOR resulter IN
SELECT
s.n_id as sectionid,
s.t_name as sectionname,
COALESCE(to_char(lupd.updated, ''DD Mon''), ''None'') as secupdated,
COALESCE(rept.num,0) as secreports,
COALESCE(usr.num,0) as secusers
...etc..
--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>