[GENERAL] Oracle to PSQL function

2007-03-29 Thread Pete 

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_LotEndM_AttributeSet.LotCharEOverwrite%TYPE;
v_SerNo M_AttributeSetInstance.SerNo%TYPE;
v_SerNoStartM_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_LotEndM_AttributeSet.LotCharEOverwrite%TYPE;
v_SerNo M_AttributeSetInstance.SerNo%TYPE;
v_SerNoStartM_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,

Re: [GENERAL] Oracle to PSQL function

2007-03-29 Thread David Fetter 
On Thu, Mar 29, 2007 at 05:07:42PM +, 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 [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Oracle to PSQL function

2007-03-29 Thread Oisin Glynn 

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_LotEndM_AttributeSet.LotCharEOverwrite%TYPE;
v_SerNo M_AttributeSetInstance.SerNo%TYPE;
v_SerNoStartM_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_LotEndM_AttributeSet.LotCharEOverwrite%TYPE;
v_SerNo M_AttributeSetInstance.SerNo%TYPE;
v_SerNoStartM_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,

Re: [GENERAL] Oracle to PSQL function

2007-03-29 Thread Tom Lane 
Pete [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Oracle to PSQL function

2007-03-29 Thread Pete 

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_NameAddTEXT := '';

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_SerNoEndadempiere.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+vistamkt=en-USform=QBRE
---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/