Oracle to PSQL function

From: Pete <pmdwise(at)hotmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Oracle to PSQL function
Date: 2007-03-29 17:07:42
Message-ID: BAY115-W10E1A53498D7D54FE56B5CBE6C0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Benjamin Arai 2007-03-29 17:32:47 Re: COPY command details
Previous Message A.M. 2007-03-29 17:04:58 Re: cursors in postgres