Re: 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(at)fetter(dot)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