Re: PL/pgSQL functions and RETURN NEXT

John Sidney-Woollett <johnsw@xxxxxxxxxxxxx> · Sun, 30 Jan 2005 12:26:32 +0000

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@xxxxxxxxxxxxxx)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster