Re: Oracle to PSQL function

Hi

Thanks for the help, but

I am kind of stuck on the cursors in PL/pgSQL
I have tried
> adempiere.CUR_Attributes CURSOR ai.Value, a.Name FOR
> SELECT ai.Value, a.Name
and
> DECLARE adempiere.CUR_Attributes CURSOR FOR
> SELECT ai.Value, a.Name

but none are accepted.

From the help I understand that cursors in PL/pgSQL are different to standard cursors.

Would it be better if I created the cursor as a seperate
function first
ie
CREATE FUNCTION adempiere.CUR_Attributes(refcursor)
RETURNS refcursor
AS '
BEGIN
SELECT ai.Value, a.Name
FROM adempiere.M_AttributeInstance ai
INNER JOIN adempiere.M_Attribute a ON
(ai.M_Attribute_ID=a.M_Attribute_ID AND a.IsInstanceAttribute='Y')
WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;
END;
' LANGUAGE plpgsql;

but then how would I call this from inside my original function?

thanks
Pete

FYI What I have so far...

CREATE OR REPLACE FUNCTION adempiere.productAttribute
(
p_M_AttributeSetInstance_ID IN INTEGER
)
RETURNS TEXT
AS $$
DECLARE
v_Name TEXT := NULL;
v_NameAdd TEXT := '';

v_Lot adempiere.M_AttributeSetInstance.Lot%TYPE;
v_LotStart adempiere.M_AttributeSet.LotCharSOverwrite%TYPE;
v_LotEnd adempiere.M_AttributeSet.LotCharEOverwrite%TYPE;
v_SerNo adempiere.M_AttributeSetInstance.SerNo%TYPE;
v_SerNoStart adempiere.M_AttributeSet.SerNoCharSOverwrite%TYPE;
v_SerNoEnd adempiere.M_AttributeSet.SerNoCharEOverwrite%TYPE;
v_GuaranteeDate adempiere.M_AttributeSetInstance.GuaranteeDate%TYPE;

DECLARE adempiere.CUR_Attributes CURSOR FOR
SELECT ai.Value, a.Name
FROM adempiere.M_AttributeInstance ai
INNER JOIN adempiere.M_Attribute a ON (ai.M_Attribute_ID=a.M_Attribute_ID AND a.IsInstanceAttribute='Y')
WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;

BEGIN
.....................
_________________________________________________________________
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE