Thread: Oracle to PSQL function
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
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&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>
Attachment
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&mkt=en-US&form=QBRE