Re: Oracle to PSQL function - Mailing list pgsql-general

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