From: Frank van Vugt <ftm.van.vugt@foxi.nl>
Organization: Foxi Consultants in Technology BV
To: "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl>
Subject: Re: plpgsql howto question
Date: Thu, 23 Dec 2004 16:27:20 +0100
User-Agent: KMail/1.5.4
References: <A3D1526C98B7C1409A687E0943EAC41001EB16@obelix.askesis.nl>
In-Reply-To: <A3D1526C98B7C1409A687E0943EAC41001EB16@obelix.askesis.nl>
Cc: <pgsql-interfaces@postgresql.org>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200412231627.20577.ftm.van.vugt@foxi.nl>

Hi Joost,

> What is the correct way of doing this?

As 'always', there's more than one way of doing this, for example
 - selecting into a var and returning that var
 - selecting into a record and returning the proper field of that record
 - return the proper values immediately
 - use 'plain' sql in combination with a case statement

Some examples:

CREATE OR REPLACE FUNCTION getcustomername(text, int4)
RETURNS text AS
'
DECLARE
	my_person TEXT;
	my_company RECORD;
BEGIN
IF $2 = 1 THEN
	SELECT INTO my_person lastname FROM person WHERE objectid = $1;
	RETURN my_person;
ELSIF $2 = 2 THEN
	SELECT INTO my_company name1 from company WHERE objectid = $1;
	RETURN my_company.name1;
END IF;
END
'  LANGUAGE 'plpgsql' VOLATILE;

*****

CREATE OR REPLACE FUNCTION getcustomername(text, int4)
RETURNS text AS
'
DECLARE
	my_id ALIAS FOR $1;
	my_type ALIAS FOR $2;
BEGIN
IF my_type = 1 THEN
	RETURN my_person lastname FROM person WHERE objectid = my_id;
ELSIF my_type = 2 THEN
	RETURN name1 from company WHERE objectid = my_id;
END IF;
END
'  LANGUAGE 'plpgsql' VOLATILE;

*****

SELECT CASE
	WHEN type = 1 THEN (SELECT person)
	WHEN type = 2 THEN (SELECT company)
	ELSE null
END;

(albeit this is not plpgsql anymore)


Obviously you want to choose one of the approaches ;)


NB. Prettige kerstdagen alvast !


-- 
Best,


Frank.