Thread: Hi there, new here and have question

Hi Everyone.
I'm just subscribe to the mailing list
I'm new to Postgresql and
I have a question

I intend to make a function that returns more than a row
I tried something like below

create function listofemployeebasedondepartment(id_dept int) $$
declare
 resultset ??;
begin
    select * into resultset from employee where id_dept = id_dept;
    return resultset;
end
$$ language 'plpgsql';

I believe you get what I want
But I just couldn't finish the code since I miss something
I manage to find 'setof' but have no idea on how to use it

Any suggestion everyone?

Thank you,
Regards,
Hendra

Hi Hendra,
> create function listofemployeebasedondepartment(id_dept int) $$
> declare
>  resultset ??;
> begin
>     select * into resultset from employee where id_dept = id_dept;
>     return resultset;
> end
> $$ language 'plpgsql';
>
> I believe you get what I want
> But I just couldn't finish the code since I miss something
> I manage to find 'setof' but have no idea on how to use it
>
> Any suggestion everyone?
>
Try something like this:

CREATE OR REPLACE FUNCTION  listofemployeebasedondepartment(id_dept int)
   RETURNS SETOF employee AS
$BODY$
BEGIN

   RETURN QUERY
       SELECT
           *
       FROM
           employee
       WHERE
           id_dept = _id_dept;

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

Jan

> Any suggestion everyone?
>
Sorry, i was to fast sending this email out ;-)

change the first parameter in the first line to '_id_depth'

CREATE OR REPLACE FUNCTION  listofemployeebasedondepartment(_id_dept
int)
  RETURNS SETOF employee AS
$BODY$
BEGIN

  RETURN QUERY
      SELECT
          *
      FROM
          employee
      WHERE
          id_dept = _id_dept;

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

Jan

> 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

|>
|> -----Original Message-----
|> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Hendra
|> Sent: 8 juillet 2008 02:59
|> To: pgsql-general@postgresql.org
|> Subject: [GENERAL] Hi there, new here and have question
|>
|> Hi Everyone.
|> I'm just subscribe to the mailing list
|> I'm new to Postgresql and
|> I have a question
|>
|> I intend to make a function that returns more than a row
|> I tried something like below
|>
|> create function listofemployeebasedondepartment(id_dept int) $$
|> declare
|>  resultset ??;
|> begin
|>     select * into resultset from employee where id_dept = id_dept;
|>     return resultset;
|> end
|> $$ language 'plpgsql';
|>
|> I believe you get what I want
|> But I just couldn't finish the code since I miss something
|> I manage to find 'setof' but have no idea on how to use it
|>
|> Any suggestion everyone?
|>
|> Thank you,
|> Regards,
|> Hendra

I believe you are looking for this:
http://www.postgresql.org/docs/8.3/interactive/xfunc-sql.html#AEN40331

Good day,

Charles Simard