Skip site navigation (1) Skip section navigation (2)

Re: Oracle to PSQL function

From: David Fetter <david(at)fetter(dot)org>
To: Pete <pmdwise(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Oracle to PSQL function
Date: 2007-03-29 17:43:54
Message-ID: 20070329174353.GB27915@fetter.org (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Oisin GlynnDate: 2007-03-29 17:50:12
Subject: Re: Oracle to PSQL function
Previous:From: A. KretschmerDate: 2007-03-29 17:34:05
Subject: Re: COPY command details

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group