Skip site navigation (1) Skip section navigation (2)

Re: Pl/Pgsql function troubles: FOLLOW UP

From: "Joshua b(dot) Jore" <josh(at)greentechnologist(dot)org>
To: Jeff Post <postjeff(at)uwm(dot)edu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Pl/Pgsql function troubles: FOLLOW UP
Date: 2002-04-17 12:26:44
Message-ID: Pine.BSO.4.40.0204161840140.24267-100000@kitten.greentechnologist.org (view raw or flat)
Thread:
Lists: pgsql-novice
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Consider this:

DROP FUNCTION list_of_membership(INTEGER,TEXT);
CREATE FUNCTION list_of_membership(INTEGER,TEXT) RETURNS TEXT AS '
DECLARE
	membership_rec RECORD;
	membership TEXT;
BEGIN
	FOR membership_rec IN EXECUTE
		''SELECT name FROM org_details WHERE person_id = ''::text
		|| CAST($1 AS TEXT)
		|| '' AND type = ''::text
		|| quote_literal($2)
		|| '' ORDER BY name;'';
	LOOP
		IF membership IS NULL THEN
			membership := membership_rec.name;
		ELSE
			membership := membership
				|| '', ''::text
				|| membership_rec.name;
		END IF;
	END LOOP;
	RETURN membership;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);


And here is how you want to be calling the function.
SELECT list_of_membership(1,'department');

Joshua b. Jore
http://www.greentechnologist.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8vWoHfexLsowstzcRAp26AJ4jqyb9Mk3VpPvpq3WMrhab4G2ceACg8ibr
R+ljghBHsTa5k9JFFuf6bNA=
=uUP3
-----END PGP SIGNATURE-----


In response to

pgsql-novice by date

Next:From: RobDate: 2002-04-17 12:30:36
Subject: Email in postgres
Previous:From: Andrew McMillanDate: 2002-04-17 10:52:21
Subject: Re: Email in postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group