Pl/Pgsql function troubles

From: Jeff Post <postjeff(at)uwm(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: Pl/Pgsql function troubles
Date: 2002-04-15 21:53:48
Message-ID: 1018907628.3cbb4becb9c15@www.panthermail.uwm.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I am hoping that I can write a function that will return a comma seperated list
of memberships in a given type ($2) of organiztions for a given userid ($1).
Memberships are available in a view called org_details where all groups list
all there members and types.

I would think that the following should work. This is the first time that I
have had to use a dynamic query so that could be the source of the problem
(like of understanding) but in the following the function never goes into the
FOR LOOP.

anybody now what I am doing wrong?

maybe a record cannot be returned that points to a view relation?????

all I am trying to do with the raise notice is spit out the Select statment
that the function is using (earlier source of trouble)

CREATE or replace FUNCTION list_of_membership(integer,CHAR) RETURNS TEXT AS '
DECLARE
membership_rec record;
membership text := NULL;
count integer := 0;
BEGIN
FOR membership_rec IN EXECUTE select name from org_details where person_id =
$1 and type = $2 order by name LOOP
RAISE NOTICE ''select name from org_details where person_id = '' || $1
|| '' and type = \''' || $2 || ''\' order by name'' ;
count := count + 1;
IF count = 1 THEN
membership := membership_rec.name;
ELSE
membership := membership || '', '' || membership_rec.name;
END IF;
END LOOP;
RETURN membership;
END;
' LANGUAGE 'plpgsql';

Thanks,

Jeff Post

Browse pgsql-novice by date

  From Date Subject
Next Message Brian Johnson 2002-04-16 15:25:06 Adding two select statements together
Previous Message Travis Hoyt 2002-04-15 20:17:10 Re: MAX(column1),MAX(column2),...