Question on Function

Dev Shed Forums Sponsor:

September 19th, 2012, 02:16 PM

rkarady

Registered User

Join Date: Sep 2012

Posts: 2

Time spent in forums: 4 m 25 sec
Reputation Power: 0

Question on Function

I am trying to create a function to list all the employee's first name . When I issue the command , I get the result set enclosed with parenthesis . How to display the result set as a list.

Please see example below and advise.

Thanks in advance.

create table emp
( empno integer ,
empfname varchar(50) ,
emplname varchar(50)
);

insert into emp values ( 1 , 'EF' , 'Codd') ;
insert into emp values ( 2 , 'Alan' , 'Turing') ;
insert into emp values ( 3 , 'Al' , 'Biruni ') ;

CREATE OR REPLACE FUNCTION list_all_emps()
RETURNS SETOF character AS
$BODY$
DECLARE
rec record;
BEGIN
FOR rec IN (Select empfname from emp order by empfname asc ) LOOP
RETURN NEXT rec;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

Select * from list_all_emps()

"(Al)"
"(Alan)"
"(EF)"

September 19th, 2012, 03:17 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 53 m 13 sec
Reputation Power: 284

Quote:

Originally Posted by rkarady

I am trying to create a function to list all the employee's first name . When I issue the command , I get the result set enclosed with parenthesis

That's because you are returning a "record" (albeit with a single column) not scalar value.

Instead of RETURN NEXT rec; you need to use RETURN NEXT rec.empfname;

Like this:

Code:

CREATE OR REPLACE FUNCTION list_all_emps()
RETURNS SETOF character AS
$BODY$
DECLARE
  rec record;
BEGIN
  FOR rec IN (Select empfname from emp order by empfname asc ) LOOP
    RETURN NEXT rec.empfname;
  END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

Are you aware that you can have this a lot simpler by using a SQL function instead of a PL/pgSQL function?

Code:

CREATE OR REPLACE FUNCTION list_all_emps()
RETURNS SETOF character AS
$body$
  SELECT empfname 
  FROM emp 
  ORDER BY empfname ASC;
$body$
LANGUAGE sql

__________________
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code

Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html

September 19th, 2012, 07:20 PM

rkarady

Registered User

Join Date: Sep 2012

Posts: 2

Time spent in forums: 4 m 25 sec
Reputation Power: 0

Thanks

Thank you very much. Is there any disadvantage of using SQL function ?

Question on Function

Developer Shed Advertisers and Affiliates