Re: PL/pgSQL functions and RETURN NEXT

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(at)postgresql(dot)org)