Re: PL/pgSQL functions and RETURN NEXT

Craig Bryden wrote:
> Hi
> Firstly, let me say that I am a newbie to PostgreSQL.
>
> I have written a PL/pgSQL function that will return a set of results. I have
> included the code below
>
> ****************************************************************************
> *******************************
> CREATE TYPE pr_SomeFunction_ReturnType as (ID smallint,
> TypeID smallint,
> Name varchar(50),
> Description varchar(500),
> TypeName varchar(20));
>
> CREATE OR REPLACE FUNCTION pr_SomeFunction (p_TypeID smallint)
> RETURNS setof pr_SomeFunction_ReturnType
> AS
> $$
> DECLARE
> r_Return pr_SomeFunction_ReturnType;
> BEGIN
>
> SELECT l.ID, l.TypeID, l.Name, l.Description, lt.Name as TypeName
> INTO r_Return
> FROM tb_Item l
> JOIN tb_ItemType lt
> ON l.TypeID = lt.TypeID;
>
> RETURN NEXT r_Return;
> RETURN;
> END;
> $$ LANGUAGE 'plpgsql';
>
>
> ****************************************************************************
> *******************************
>
> When I run "select * from pr_SomeFunction(1::smallint);", I only get one
> record back, instead of two.

You need a loop construct here:

FOR r_return IN SELECT l.ID, l.TypeID, l.Name, l.Description, lt.Name as
TypeName FROM tb_Item l JOIN tb_ItemType lt USING (TypeID) LOOP
    RETURN NEXT r_Return;
END LOOP;
RETURN;

HTH,

Sven