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 ;)
|