By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,455 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

pgplsql - Oracle nvl

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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 !

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2

P: n/a
On Wed, Aug 27, 2003 at 09:58:00 +0200,
Christian Traber <ch*******@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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #3

P: n/a
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....l.html#AEN9753
http://www.postgresql.org/docs/view....l.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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #4

P: n/a
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....l.html#AEN9753
http://www.postgresql.org/docs/view....l.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

Nov 11 '05 #5

P: n/a
You don't need to build any function for this, you have them ready:
http://www.postgresql.org/docs/view....l.html#AEN9753
http://www.postgresql.org/docs/view....l.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 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.