Thread: help me please with function

help me please with function

From
andriy neverenchuk
Date:
Help me please with this function writed in MSSQL. I want to pass on Postgres

CREATE OR REPLACE FUNCTION GetProductsByCategoryId
(IN CategoryId int, IN PageIndex int, IN NumRows int,
OUT CategoryName varchar(50), OUT CategoryProductCount int)
 RETURNS SETOF RECORD LANGUAGE plpgsql as $$

DECLARE
  startRowIndex int;
BEGIN
    /*
       The below statements enable returning the Total Product Count and friendly Name for the CategoryId
       as output paramsters to our SPROC.  This enables us to avoid having to make a separate call to the
       database to retrieve them, and can help improve performance quite a bit
    */
   
    CategoryProductCount = (SELECT COUNT(*) FROM Products where Products.CategoryId = CategoryId)
    CategoryName = (SELECT Name FROM Categories Where Categories.CategoryID = CategoryId)
   
    /*
       The below statements use the new ROW_NUMBER() function in SQL 2005 to return only the specified
       rows we want to retrieve from the Products table
    */   
   
    --Declare @startRowIndex INT;
    --set @startRowIndex = (@PageIndex * @NumRows) + 1;
   
    startRowIndex := (PageIndex * NumRows) + 1;

    With ProductEntries as (
        (SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as Row, ProductId, CategoryId, Description, ProductImage, UnitCost
        FROM Products WHERE CategoryId=CategoryId)
    )
   
    (Select ProductId, CategoryId, Description, ProductImage, UnitCost
    FROM ProductEntries WHERE Row between startRowIndex and StartRowIndex+NumRows-1)
   
END;
$$;

I have a problem whis instraction

With ProductEntries as (
        (SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as Row, ProductId, CategoryId, Description, ProductImage, UnitCost
        FROM Products WHERE CategoryId=CategoryId)
    )

I can not find command in postgres - With ... as

My changed function is

CREATE OR REPLACE FUNCTION "public"."getproductsbycategoryid_refcursor" (categoryid integer, pageindex integer, numrows integer, out product_id integer, out category_id integer, out description varchar, out product_image varchar, out unit_cost varchar) RETURNS SETOF record AS
$body$
DECLARE
 startrowindex int;
 categoryproductcount int;
 categoryname varchar(50);

BEGIN

SELECT COUNT(*) INTO categoryproductcount FROM products where products."category_id"=categoryid;
SELECT "name" INTO categoryname FROM categories Where categories."category_id"=categoryid;

 startrowindex = (pageindex * numrows) + 1;

-- ERROR HERE
With productentries as
SELECT ROW_NUMBER() OVER (ORDER BY products."category_id" ASC) as Row,
products."product_id", products."category_id", products."description",
products."product_image", products."unit_cost"
FROM products WHERE products."category_id"=categoryid;

RETURN QUERY SELECT productentries."product_id", productentries."category_id",
productentries."description", productentries."product_image", productentries."unit_cost"
FROM productentries WHERE productentries."row" between startrowindex and startrowindex+numrows-1;


END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;


Help me please, excuse me for bad inglish ;)




Re: help me please with function

From
Thomas Kellerer
Date:
> I can not find command in postgres - With ... as

You need Postgres 8.4 for that:

http://www.postgresql.org/docs/current/static/queries-with.html

Thomas