#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Red face 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)"
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Thanks


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

IMN logo majestic logo threadwatch logo seochat tools logo