Oracle to PSQL function

On Thu, Mar 29, 2007 at 05:07:42PM +0000, 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

This should read IN INTEGER

)
RETURN VARCHAR2

This should read RETURNS TEXT

AS

Need a start of the function body, e.g. $$, and then a DECLARE here

v_Name VARCHAR2(2000) := NULL;
v_NameAdd VARCHAR2(2000) := '';

The above should be TEXT

Anyhow, that should get you started.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

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&amp;mkt=en-US&amp;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>

Pete <pmdwise@hotmail.com> writes:

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

Try
http://www.postgresql.org/docs/8.2/interactive/plpgsql.html
particularly
http://www.postgresql.org/docs/8.2/interactive/plpgsql-porting.html

plpgsql tries to be like Oracle's pl/sql, but that only carries as far
as the function body. The outer CREATE FUNCTION syntax is not under
its control and is significantly different from Oracle's.

regards, tom lane

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&amp;mkt=en-US&amp;form=QBRE