Pl/Pgsql function troubles: FOLLOW UP

From: Jeff Post <postjeff(at)uwm(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: Pl/Pgsql function troubles: FOLLOW UP
Date: 2002-04-16 21:58:14
Message-ID: 1018994294.3cbc9e7668075@www.panthermail.uwm.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

here is Code that I got working but I still have some problems with:
(I am asking for help, so if you wish to skip to my question read the last
sentance)

CREATE or replace FUNCTION list_of_membership(integer,CHAR) RETURNS TEXT AS '
DECLARE
membership_rec record;
membership text := NULL;
count integer := 0;
sqlstr1 text := ''select name from org_details where person_id = '';
sqlstr2 text := '' and type = '';
sqlstr3 text := '' order by name;'';
BEGIN
FOR membership_rec IN EXECUTE sqlstr1 || $1 || sqlstr2 || $2 || sqlstr3
LOOP
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';

the problems are such:
after the above is "compiled" and I try to do a select from it:

Attempt number 1:
=# SELECT list_of_membership(1,department);
ERROR: Attribute 'department' not found

This is my ideal methodfor calling this function. As you can see it has a
problem with the second variable pass. The problem (after much head scratching)
is determined to be that it thinks I am trying to pass it a defined type. when
I am supposed to be passing it a string.

Attempt number 2:
=# SELECT list_of_membership(1,'department');
NOTICE: Error occurred while executing PL/pgSQL function list_of_membership
NOTICE: line 10 at for over execute statement
ERROR: Attribute 'department' not found

Well this is esentually the same problem as above. But now in the internal
SELECT sql.

Attempt number 3:
=# SELECT list_of_membership(1,'\'department\'');
list_of_membership
---------------------
Madison, Technology
(1 row)

YEA!!!!

But MAN is that an UGLY call (especialy since I will be building this into perl
scripts.) the '\' and \'' are going to cause me some trouble.

Does anyone have any suggestions as to how I can make this look more like
attempt number 1 or 2 ?

THANKS!
Jeff Post

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Oliver Elphick 2002-04-16 22:37:30 Re: Pl/Pgsql function troubles: FOLLOW UP
Previous Message P. Jourdan 2002-04-16 20:19:50 cannot restore db