Thread: PL/pgSQL functions and RETURN NEXT

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. In the tb_Items table, there are two records
that meet the criteria, and if I run the query on it's own (ouside a
function), I do get two records in the results.

Any help with understanding the usage of RETURN NEXT will be greatly
appreciated.

Thanks
Craig

Here's an example that I butchered to cut it down to size that should
illustrate what you need to do (basically use a LOOP construct)

CREATE TYPE customer.InvoiceItem AS (
   WCCustOrderID     varchar(16),
   OrderDate         date,
   Currency          varchar(3),
   TaxCode           varchar(3),
   TaxRate           numeric(10,3),
   Net               numeric(10,2),
   Tax               numeric(10,2),
   Gross             numeric(10,2)
);

CREATE OR REPLACE FUNCTION CUSTOMER.GetInvoiceStats(integer, integer)
RETURNS setof customer.InvoiceItem AS '
   -- generates invoice info for the year and month from the
   -- completed orders
DECLARE
   vInv          customer.InvoiceItem%rowtype;
   vCustOrder    record;
   vStartDate    date;
   vEndDate      date;
BEGIN
   -- build the start and end dates
   vStartDate := to_date(''1-''||pMonth||''-''||pYear,''dd-mm-yyyy'');
   IF (pMonth > 11) then
     vEndDate := to_date(''1-1''||''-''||1+pYear,''dd-mm-yyyy'');
   ELSE
     vEndDate := to_date(''1-''||1+pMonth||''-''||pYear,''dd-mm-yyyy'');
   END IF;

   FOR vCustOrder IN
     SELECT * from customer.WCCustOrder
     WHERE OrderDate >= vStartDate AND OrderDate < vEndDate
     AND WCCustOrderStatusID = 9
   LOOP
     vInv.WCCustOrderID := vCustOrder.WCCustOrderID;
     vInv.OrderDate := vCustOrder.OrderDate::date;
     vInv.Currency := vCustOrder.Currency;
     vInv.TaxCode := vCustOrder.WSTaxCode;
     vInv.TaxRate := vCustOrder.TaxRate;
     vInv.Gross := round(vCustOrder.Gross,2);
     vInv.Net := round(vCustOrder.Net,2);
     vInv.Tax := round(vCustOrder.Gross - vInv.Net,2);

     RETURN NEXT vInv;
   END LOOP;

   return;
END;
' LANGUAGE 'plpgsql';

Hope that helps.

John Sidney-Woollett

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. In the tb_Items table, there are two records
> that meet the criteria, and if I run the query on it's own (ouside a
> function), I do get two records in the results.
>
> Any help with understanding the usage of RETURN NEXT will be greatly
> appreciated.
>
> Thanks
> Craig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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