Обсуждение: pgplsql - Oracle nvl
Hi, I'll try to switch from Oracle to postgres for some small applications. Is it possible to build functions like Oracle's nvl or decode with pgplsql? How can I make a function like nvl that works for every datatype? Best regards, Christian
Hi, > > I'll try to switch from Oracle to postgres for some small applications. > Is it possible to build functions like Oracle's nvl or decode with > pgplsql? > How can I make a function like nvl that works for every datatype? > > Best regards, > Christian Try coalesce. The syntax is the same as nvl. Jeff
>Write as many "nvl" functions as you have different types of params. >Overloading works just fine in postgres. (I do not know what function "nvl" >actually does, so maybe You can explain it.) > > It's the same as coalesce in postgres. If overloading works, my problem is solved:-) Thanks, Christian
On Wednesday 27 August 2003 09:58, Christian Traber wrote: > Hi, > > I'll try to switch from Oracle to postgres for some small applications. > Is it possible to build functions like Oracle's nvl or decode with pgplsql? > How can I make a function like nvl that works for every datatype? > Write as many "nvl" functions as you have different types of params. Overloading works just fine in postgres. (I do not know what function "nvl" actually does, so maybe You can explain it.) for example : CREATE OR REPLACE FUNCTION nvl( INTEGER ) AS ... CREATE OR REPLACE FUNCTION nvl( TEXT ) AS ... Regards !
On Wed, Aug 27, 2003 at 09:58:00 +0200, Christian Traber <christian@traber-net.de> wrote: > Hi, > > I'll try to switch from Oracle to postgres for some small applications. > Is it possible to build functions like Oracle's nvl or decode with pgplsql? > How can I make a function like nvl that works for every datatype? You can use coalesce to do what nvl does.
OK, clear. But if you have access to your application code, it's maybe better to create an abstraction layer in front of the DB specific SQLs, and implement for each DB differently (this is how we do it). The "nvl" and "decode" statements are Oracle specific, so I can't see why not use the Postgres specific syntax for Postgres. A lot of Oracle queries will work a lot better on postgres if rewritten differently, and some of them can be even expressed more elegantly in Postgres. AFAIKT, designing your application so it can use a DB abstraction layer will give you far less trouble in the long run than trying to make the Oracle queries work unchanged on Postgres... Cheers, Csaba. On Wed, 2003-08-27 at 10:55, Christian Traber wrote: > I know this functions, but I do not want to change the statements in my > old application. > So I want to make the functions with the Oracle names for compatibility. > > Best regards, > Christian > > Csaba Nagy wrote: > > >You don't need to build any function for this, you have them ready: > >http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-conditional.html#AEN9753 > >http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-conditional.html#AEN9698 > > > >Cheers, > >Csaba. > > > >On Wed, 2003-08-27 at 09:58, Christian Traber wrote: > > > > > >>Hi, > >> > >>I'll try to switch from Oracle to postgres for some small applications. > >>Is it possible to build functions like Oracle's nvl or decode with pgplsql? > >>How can I make a function like nvl that works for every datatype? > >> > >>Best regards, > >>Christian > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 6: Have you searched our list archives? > >> > >> http://archives.postgresql.org > >> > >> > > > > > > > > > > >
I know this functions, but I do not want to change the statements in my old application. So I want to make the functions with the Oracle names for compatibility. Best regards, Christian Csaba Nagy wrote: >You don't need to build any function for this, you have them ready: >http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-conditional.html#AEN9753 >http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-conditional.html#AEN9698 > >Cheers, >Csaba. > >On Wed, 2003-08-27 at 09:58, Christian Traber wrote: > > >>Hi, >> >>I'll try to switch from Oracle to postgres for some small applications. >>Is it possible to build functions like Oracle's nvl or decode with pgplsql? >>How can I make a function like nvl that works for every datatype? >> >>Best regards, >>Christian >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 6: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> > > > > >
You don't need to build any function for this, you have them ready: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-conditional.html#AEN9753 http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-conditional.html#AEN9698 Cheers, Csaba. On Wed, 2003-08-27 at 09:58, Christian Traber wrote: > Hi, > > I'll try to switch from Oracle to postgres for some small applications. > Is it possible to build functions like Oracle's nvl or decode with pgplsql? > How can I make a function like nvl that works for every datatype? > > Best regards, > Christian > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
If you want to keep the same functions just do as follows CREATE OR REPLACE FUNCTION nvl(TEXT,TEXT) RETURNS TEXT AS ' BEGIN RETURN COALESCE($1,$2); END;' LANGUAGE 'plpgsql'; You will have to do this for all supported data types unless you want to put in a third parameter that would be the cast type HTH Darren On Wed, 27 Aug 2003, Christian Traber wrote: > I know this functions, but I do not want to change the statements in my > old application. > So I want to make the functions with the Oracle names for compatibility. > > Best regards, > Christian > > Csaba Nagy wrote: > > >You don't need to build any function for this, you have them ready: > >http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-conditional.html#AEN9753 > >http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-conditional.html#AEN9698 > > > >Cheers, > >Csaba. > > > >On Wed, 2003-08-27 at 09:58, Christian Traber wrote: > > > > > >>Hi, > >> > >>I'll try to switch from Oracle to postgres for some small applications. > >>Is it possible to build functions like Oracle's nvl or decode with pgplsql? > >>How can I make a function like nvl that works for every datatype? > >> > >>Best regards, > >>Christian > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 6: Have you searched our list archives? > >> > >> http://archives.postgresql.org > >> > >> > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Darren Ferguson