CREATE OR REPLACE FUNCTION listofemployeebasedondepartment(_id_dept
int)
RETURNS SETOF record AS
$BODY$
DECLARE
  empdata record;
BEGIN

RETURN QUERY
 SELECT
 e.*, d.department_name
 FROM
 employee e, dept d
 WHERE
 e.id_dept = d.id AND
 e.id_dept = _id_dept;

RETURN;
 END;
 $BODY$
 LANGUAGE 'plpgsql';

I can call it by
SELECT listofemployeebasedondepartment(dept_id)
and it gives me return value a set of record,
but when I want to get just one field of those record,
for example
SELECT name FROM listofemployeebasedondepartment(dept_id)
psql gives me error that I don't have column-list or something like that
How to achieve such result?

hi hendry,

simple example:

CREATE OR REPLACE FUNCTION test1(_id int)
  RETURNS SETOF record AS
$BODY$
DECLARE rec record;
BEGIN
        FOR rec IN
                SELECT
                        a.foo, b.bar
                FROM
                        a, b
                WHERE
                        a.id = _id
                        AND a.id = b.id
        LOOP
        RETURN NEXT rec;
        END LOOP;

        RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql'

you have to specify the columns when you call your function something like this:

select * from test1(1) as (foo text, bar text);

Jan


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to