dimanche 28 juin 2015

how to return more than 1 row in PostgreSQL function?

Basicly my quesion is how to do this How to return more than 1 record? in PostgreSQL.

I have a query which returns 80 rows, and I want to put it in a function.

I defined a type:

CREATE TYPE typ_new AS
   (id integer,
    pn text,
    name citext,
    qty numeric,);

and I wrote a function :

CREATE OR REPLACE FUNCTION stock.func()
  RETURNS typ_new AS
  $BODY$
  begin

            QUERY

end;
 $BODY$
 LANGUAGE plpgsql VOLATILE

but it returns only 1 row instead of 80. I know I should do some sort of loop because I need to add each row to the return type but I just can find the proper syntax to do that.

I'm trying to do what is written here under 39.6.1.2. RETURN NEXT and RETURN QUERY but I don't understand what is foo%rowtype , I know it's 99% of the answer but I just can't make it work...

Aucun commentaire:

Enregistrer un commentaire