Re: Oracle to PSQL function

Oisin Glynn <me@xxxxxxxxxxxxxx> · Thu, 29 Mar 2007 12:50:12 -0500

Pete wrote:
Hi

I am trying to do an upgrade on an open source app called adempiere The problem I have is that the 
original scripts are for Oracle and I am trying to run it on a PostgreSQLl data base

I am having a problem converting  the 007_ProductAttribute.sql script. See below
I am getting the following error.  

ERROR: syntax error at or near "v_Name" 
SQL state: 42601 
Character: 1263 

I have tried all kinds of variations
RETURNS VARCHAR(2) 
AS 'v_Name
....................
WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;' 
BEGIN 

RETURNS VARCHAR(2) 
AS "v_Name
....................
WHERE ai.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;" 
BEGIN 

but none seem to work

I am looking for an on line help with loads of examples for PSQL 
specifically in regards to stored procedures or functions.

The following does not help me much.
http://www.postgresql.org/docs/8.2/interactive/sql-createfunction.html

any help would be appreciated

Thank you

007_ProductAttribute.sql
==================
CREATE OR REPLACE FUNCTION productAttribute
(
    p_M_AttributeSetInstance_ID     IN NUMBER
)
RETURN VARCHAR2
AS
    v_Name          VARCHAR2(2000) := NULL;
    v_NameAdd       VARCHAR2(2000) := '';
    --
    v_Lot           M_AttributeSetInstance.Lot%TYPE;
    v_LotStart      M_AttributeSet.LotCharSOverwrite%TYPE;
    v_LotEnd        M_AttributeSet.LotCharEOverwrite%TYPE;
    v_SerNo         M_AttributeSetInstance.SerNo%TYPE;
    v_SerNoStart    M_AttributeSet.SerNoCharSOverwrite%TYPE;
    v_SerNoEnd      M_AttributeSet.SerNoCharEOverwrite%TYPE;
    v_GuaranteeDate M_AttributeSetInstance.GuaranteeDate%TYPE;
    --
    CURSOR CUR_Attributes IS
        SELECT ai.Value, a.Name
        FROM M_AttributeInstance ai
          INNER JOIN 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
/*    --  Get Product Name
    SELECT Name 
      INTO v_Name
    FROM M_Product WHERE M_Product_ID=p_M_Product_ID;
*/
    --  Get Product Attribute Set Instance
    IF (p_M_AttributeSetInstance_ID > 0) THEN
        SELECT asi.Lot, asi.SerNo, asi.GuaranteeDate,
            COALESCE(a.SerNoCharSOverwrite, TO_NCHAR('#')), COALESCE(a.SerNoCharEOverwrite, TO_NCHAR('')),
            COALESCE(a.LotCharSOverwrite, TO_NCHAR('�')), COALESCE(a.LotCharEOverwrite, TO_NCHAR('�'))
          INTO v_Lot, v_SerNo, v_GuaranteeDate,
            v_SerNoStart, v_SerNoEnd, v_LotStart, v_LotEnd
        FROM M_AttributeSetInstance asi
          INNER JOIN M_AttributeSet a ON (asi.M_AttributeSet_ID=a.M_AttributeSet_ID)
        WHERE asi.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;
        --
        IF (v_SerNo IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_SerNoStart || v_SerNo || v_SerNoEnd || ' ';
        END IF;
        IF (v_Lot IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_LotStart || v_Lot || v_LotEnd || ' ';
        END IF;
        IF (v_GuaranteeDate IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_GuaranteeDate || ' ';
        END IF;
        --
        FOR a IN CUR_Attributes LOOP
            v_NameAdd := v_NameAdd || a.Name || ':' || a.Value || ' ';
        END LOOP;
        --
        IF (LENGTH(v_NameAdd) > 0) THEN
            v_Name := v_Name || ' (' || TRIM(v_NameAdd) || ')';
        END IF;
    END IF;

    RETURN v_Name;
END productAttribute;

New 007_ProductAttribute.sql
======================

CREATE OR REPLACE FUNCTION adempiere.productAttribute
(
    p_M_AttributeSetInstance_ID     IN NUMBER
)
RETURN VARCHAR

AS
    v_Name          VARCHAR(2000) := NULL;
    v_NameAdd       VARCHAR(2000) := '';
    --
    v_Lot           M_AttributeSetInstance.Lot%TYPE;
    v_LotStart      M_AttributeSet.LotCharSOverwrite%TYPE;
    v_LotEnd        M_AttributeSet.LotCharEOverwrite%TYPE;
    v_SerNo         M_AttributeSetInstance.SerNo%TYPE;
    v_SerNoStart    M_AttributeSet.SerNoCharSOverwrite%TYPE;
    v_SerNoEnd      M_AttributeSet.SerNoCharEOverwrite%TYPE;
    v_GuaranteeDate M_AttributeSetInstance.GuaranteeDate%TYPE;
    --
    CURSOR CUR_Attributes IS
        SELECT ai.Value, a.Name
        FROMadempiere. 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
/*    --  Get Product Name
    SELECT Name 
      INTO v_Name
    FROM M_Product WHERE M_Product_ID=p_M_Product_ID;
*/
    --  Get Product Attribute Set Instance
    IF (p_M_AttributeSetInstance_ID > 0) THEN
        SELECT asi.Lot, asi.SerNo, asi.GuaranteeDate,
            COALESCE(a.SerNoCharSOverwrite, TO_NCHAR('#')), COALESCE(a.SerNoCharEOverwrite, TO_NCHAR('')),
            COALESCE(a.LotCharSOverwrite, TO_NCHAR('�')), COALESCE(a.LotCharEOverwrite, TO_NCHAR('�'))
          INTO v_Lot, v_SerNo, v_GuaranteeDate,
            v_SerNoStart, v_SerNoEnd, v_LotStart, v_LotEnd
        FROM adempiere.M_AttributeSetInstance asi
          INNER JOIN adempiere.M_AttributeSet a ON (asi.M_AttributeSet_ID=a.M_AttributeSet_ID)
        WHERE asi.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;
        --
        IF (v_SerNo IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_SerNoStart || v_SerNo || v_SerNoEnd || ' ';
        END IF;
        IF (v_Lot IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_LotStart || v_Lot || v_LotEnd || ' ';
        END IF;
        IF (v_GuaranteeDate IS NOT NULL) THEN
            v_NameAdd := v_NameAdd || v_GuaranteeDate || ' ';
        END IF;
        --
        FOR a IN CUR_Attributes LOOP
            v_NameAdd := v_NameAdd || a.Name || ':' || a.Value || ' ';
        END LOOP;
        --
        IF (LENGTH(v_NameAdd) > 0) THEN
            v_Name := v_Name || ' (' || TRIM(v_NameAdd) || ')';
        END IF;
    END IF;

    RETURN v_Name;
END adempiere.productAttribute;
/
_________________________________________________________________
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

I think you are missing the declare  piece this is from the 8.1 docs. 
Unfortunately the user comments (often nice examples) do not transfer 
from version to version in the docs. I tend to change the url from 8.2 
to 8.1 to 8.0 etc etc to see if any better user notes are already there.

Oisin

CREATE OR REPLACE FUNCTION foo()
 RETURNS void
AS $$
DECLARE
 V_StartTime timestamp with time zone;
 V_EndTime timestamp with time zone;
BEGIN
 SELECT INTO V_StartTime, V_EndTime
        P_StartTime, P_EndTime
   FROM normalize_time_period_limit(NULL::timestamp with time zone, 
NULL::timestamp with time zone);
END;
$$ LANGUAGE PLPGSQL;

--
Oisin Glynn My status <skype:oisinglynn?call>
Attachment:
smime.p7s

Description: S/MIME Cryptographic Signature