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

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>