Thread: plpgsql howto question

Hi all,

I want to use a plpgsql function that returns the value of a column, depending on the arguments ( the code below does
notwork, hence the question): 

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

What is the correct way of doing this?

TIA

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.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-
selectinginto a record and returning the proper field of that record- return the proper values immediately- use 'plain'
sqlin combination with a case statement
 

Some examples:

CREATE OR REPLACE FUNCTION getcustomername(text, int4)
RETURNS text AS
'
DECLAREmy_person TEXT;my_company RECORD;
BEGIN
IF $2 = 1 THENSELECT INTO my_person lastname FROM person WHERE objectid = $1;RETURN my_person;
ELSIF $2 = 2 THENSELECT 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
'
DECLAREmy_id ALIAS FOR $1;my_type ALIAS FOR $2;
BEGIN
IF my_type = 1 THENRETURN my_person lastname FROM person WHERE objectid = my_id;
ELSIF my_type = 2 THENRETURN name1 from company WHERE objectid = my_id;
END IF;
END
'  LANGUAGE 'plpgsql' VOLATILE;

*****

SELECT CASEWHEN 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.