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
