Re: Hi there, new here and have question

> 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