Re: Oracle to PSQL function

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>