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
