| 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: | Whole Thread | Raw Message | 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
| 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),... |