From: | Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> |
---|---|
To: | "Joost Kraaijeveld" <J(dot)Kraaijeveld(at)Askesis(dot)nl> |
Cc: | <pgsql-interfaces(at)postgresql(dot)org> |
Subject: | Re: plpgsql howto question |
Date: | 2004-12-23 15:27:20 |
Message-ID: | 200412231627.20577.ftm.van.vugt@foxi.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-interfaces |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Wimmer | 2004-12-28 21:50:09 | type cast/validation functions |
Previous Message | Joost Kraaijeveld | 2004-12-23 14:19:54 | plpgsql howto question |