Обсуждение: Oracle Decode Function

Hello,

I would like to implement a function similar to the Decode function in
Oracle.  I was wondering if it is possible to accept a variable number
of parameters (array??).

Thanks,
Edwin S. Ramirez

 > I would like to implement a function similar to the Decode function in> Oracle.

Take a look at the CASE WHEN ... THEN functionality. For example:

Oracle:
select decode(col1,'abc',1,'xyz',2,0) from test;

Postgresql:
select case when col1 = 'abc' then 1 when col1 = 'xyz' then 2 else 0 end 
from test;
> I was wondering if it is possible to accept a variable number> of parameters (array??).

If you're asking about whether a custom function can have vararg 
parameters, the answer appears to depend on the CREATE FUNCTION syntax. 
I've never used them personally, but the PG_FUNCTION_ARGS and 
PG_GETARG_xxx(#) macros (/src/includes/fmgr.h) available for compiled 
functions would appear to support variable length argument lists. The 
problem is that I couldn't pin down a CREATE FUNCTION that provided the 
same vararg functionality. Hopefully somebody can answer this conclusively.

If it can't be done using custom functions, it should be implementable 
"internally" using the same concepts used to support the IN() function 
so maybe take a look in /src/backend/parser/parse_func.c for a start.


Edwin S. Ramirez wrote:
> Hello,
> 
> I would like to implement a function similar to the Decode function in
> Oracle.  I was wondering if it is possible to accept a variable number
> of parameters (array??).
> 
> Thanks,
> Edwin S. Ramirez
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

Marc Lavergne <mlavergne-pub@richlava.com> writes:
> If you're asking about whether a custom function can have vararg 
> parameters, the answer appears to depend on the CREATE FUNCTION
> syntax. 

Can't do it, though you could imagine creating a family of functions
of the same name and different numbers of parameters.  Trying to
emulate DECODE this way would have a much worse problem: what's the
datatype of the parameters?  (Or the result?)

Use CASE; it does more than DECODE *and* is ANSI-standard.
        regards, tom lane

That would get ugly in a real hurry! Oracle does get around the issue of 
parameter datatypes by having automatic datatype conversions, more or 
less, everything becomes a varchar2. The only real attractants to 
implementing a DECODE() function is that it's one less thing to convert 
when migrating apps from Oracle and, unfortunately, this is also a piece 
of the SQL*Net compatibility that I'm looking into doing!


Tom Lane wrote:
> Marc Lavergne <mlavergne-pub@richlava.com> writes:
> 
>>If you're asking about whether a custom function can have vararg 
>>parameters, the answer appears to depend on the CREATE FUNCTION
>>syntax. 
> 
> 
> Can't do it, though you could imagine creating a family of functions
> of the same name and different numbers of parameters.  Trying to
> emulate DECODE this way would have a much worse problem: what's the
> datatype of the parameters?  (Or the result?)
> 
> Use CASE; it does more than DECODE *and* is ANSI-standard.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

if you find yourself using the decode statement, you are probably
doing something wrong. why have it, do you _need_ it?

if you are using it for display strings based on conditions, 
you shouldnt be using a function to do this. it should be a table,
or something in the middle layer. try to keep the frame of mind of
letting the db do it's job of just managing data; middle layer for
doing something useful with the data and sending to the top layer
for presentation or formatted data that is meaningful there. It 
is the right(tm) way to do things, and will make life alot easier :)

would be interested to hear a valid reason why you feel the need
to use decode(). seems that oracle gives you alot of functions and
abilities that allow dba's and programmers to be lazy, instead of 
having a good db [relational] design (and that is more standards
compliant). 

though like Tom Lane said, there is case, if you need it. 
good luck!

-chris

Marc Lavergne writes:> That would get ugly in a real hurry! Oracle does get around the issue of > parameter datatypes
byhaving automatic datatype conversions, more or > less, everything becomes a varchar2. The only real attractants to >
implementinga DECODE() function is that it's one less thing to convert > when migrating apps from Oracle and,
unfortunately,this is also a piece > of the SQL*Net compatibility that I'm looking into doing!> > > Tom Lane wrote:> >
MarcLavergne <mlavergne-pub@richlava.com> writes:> > > >>If you're asking about whether a custom function can have
vararg> >>parameters, the answer appears to depend on the CREATE FUNCTION> >>syntax. > > > > > > Can't do it, though
youcould imagine creating a family of functions> > of the same name and different numbers of parameters.  Trying to> >
emulateDECODE this way would have a much worse problem: what's the> > datatype of the parameters?  (Or the result?)> >
>> Use CASE; it does more than DECODE *and* is ANSI-standard.> > > >             regards, tom lane> > > >
---------------------------(endof broadcast)---------------------------> > TIP 1: subscribe and unsubscribe commands go
tomajordomo@postgresql.org> > > > > > ---------------------------(end of broadcast)---------------------------> TIP 5:
Haveyou checked our extensive FAQ?> > http://www.postgresql.org/users-lounge/docs/faq.html
 

> would be interested to hear a valid reason why you feel the need> to use decode().

Just let me start by saying that this is NOT for me (see the original 
email in thread)! Personally, I have no trouble using CASE. However, if 
I want to create an Oracle-compatibilty layer, I have to implement all 
Oracle functions both good and bad ... my opinion either way is totally 
irrelevant!
> if you find yourself using the decode statement, you are probably> doing something wrong. why have it, do you _need_
it?

The only place I have found DECODE/CASE to be attractive is in ORDER BY 
clauses (hunts through code for example) ... imagine an LOV based on the 
following:

create table some_list (id integer, label varchar(20), position integer);

-- defaults
insert into some_list values (-1,'Any Value',0)
insert into some_list values (0,'No Value',0)
-- values
insert into some_list values (1,'Apple',2)
insert into some_list values (2,'Orange',1)

select id, name from some_list
order by decode(id,-1,-999,0,-998,position) asc

Of course this is a highly diluted example so don't over-analyze it but 
the intent is for the "default" entries (IDs -1 and 0) to always appear 
first while giving the user the ability to change the label and position 
values (but not the id) of any row, including the defaults. In this 
case, shy of restricting the input for position based on individual row 
IDs (imagine this is a JSP app), it makes much more sense to override 
the position column using the id column and then just use a function 
based index.
> seems that oracle gives you alot of functions and> abilities that allow dba's and programmers to be lazy, instead of>
havinga good db [relational] design (and that is more standards> compliant).
 

Oh heck yeah ... of course they do that! It locks you in to their 
platform and makes migrating apps off of Oracle a more expensive 
proposition. It's a really smart move when you have a huge chunk of the 
market and you want to keep it that way. That's why converting from 
PostgreSQL to Oracle is relatively easy while the reverse is ... well 
tough. Oracle used to encourage standards but now the only thing I see 
being encouraged is lock in ... that's why I'm here! ;-)

Chris Humphries wrote:
> if you find yourself using the decode statement, you are probably
> doing something wrong. why have it, do you _need_ it?
> 
> if you are using it for display strings based on conditions, 
> you shouldnt be using a function to do this. it should be a table,
> or something in the middle layer. try to keep the frame of mind of
> letting the db do it's job of just managing data; middle layer for
> doing something useful with the data and sending to the top layer
> for presentation or formatted data that is meaningful there. It 
> is the right(tm) way to do things, and will make life alot easier :)
> 
> would be interested to hear a valid reason why you feel the need
> to use decode(). seems that oracle gives you alot of functions and
> abilities that allow dba's and programmers to be lazy, instead of 
> having a good db [relational] design (and that is more standards
> compliant). 
> 
> though like Tom Lane said, there is case, if you need it. 
> good luck!
> 
> -chris
> 
> Marc Lavergne writes:
>  > That would get ugly in a real hurry! Oracle does get around the issue of 
>  > parameter datatypes by having automatic datatype conversions, more or 
>  > less, everything becomes a varchar2. The only real attractants to 
>  > implementing a DECODE() function is that it's one less thing to convert 
>  > when migrating apps from Oracle and, unfortunately, this is also a piece 
>  > of the SQL*Net compatibility that I'm looking into doing!
>  > 
>  > 
>  > Tom Lane wrote:
>  > > Marc Lavergne <mlavergne-pub@richlava.com> writes:
>  > > 
>  > >>If you're asking about whether a custom function can have vararg 
>  > >>parameters, the answer appears to depend on the CREATE FUNCTION
>  > >>syntax. 
>  > > 
>  > > 
>  > > Can't do it, though you could imagine creating a family of functions
>  > > of the same name and different numbers of parameters.  Trying to
>  > > emulate DECODE this way would have a much worse problem: what's the
>  > > datatype of the parameters?  (Or the result?)
>  > > 
>  > > Use CASE; it does more than DECODE *and* is ANSI-standard.
>  > > 
>  > >             regards, tom lane
>  > > 
>  > > ---------------------------(end of broadcast)---------------------------
>  > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>  > > 
>  > 
>  > 
>  > 
>  > ---------------------------(end of broadcast)---------------------------
>  > TIP 5: Have you checked our extensive FAQ?
>  > 
>  > http://www.postgresql.org/users-lounge/docs/faq.html
> 

> If you're asking about whether a custom function can have vararg 
> parameters, the answer appears to depend on the CREATE FUNCTION syntax. 
> I've never used them personally, but the PG_FUNCTION_ARGS and 
> PG_GETARG_xxx(#) macros (/src/includes/fmgr.h) available for compiled 
> functions would appear to support variable length argument lists. The 
> problem is that I couldn't pin down a CREATE FUNCTION that provided the 
> same vararg functionality. Hopefully somebody can answer this 
> conclusively.

contrib/fulltextindex/fti.c uses variable numbers of arguments...

Chris

> contrib/fulltextindex/fti.c uses variable numbers of arguments...

I see the code, but maybe I don't SEE the code. I'm only on my second 
cup of coffee so I may be missing something but I am not betting any 
money in it :) Fulltextindex appears to work because it's called within 
a trigger but I don't think you can get the parser not to complain about 
arguments when your function is not called internally by the trigger 
manager. Here's my fat-free proof of concept:

-- -----------------------------------------------
-- /tmp/varargs.c

#include "postgre.h"
#include "fmgr.h"

PG_FUNCTION_INFO_V1(varargs);

Datum varargs(PG_FUNCTION_ARGS)
{  int32 v_0 = PG_GETARG_INT32(0);  int32 v_1 = PG_GETARG_INT32(1);
  PG_RETURN_INT32(v_0 + v_1);
}

-- -----------------------------------------------

gcc -Wall -L. -D_REENTRANT -fPIC -shared 
-I/home/postgre/postgresql-7.2/src/include -o /tmp/varargs.so /tmp/varargs.c

-- -----------------------------------------------
-- verify it works with arg defs

create function varargs(int4, int4) returns int4 as  '/tmp/varargs.so'  language 'C';

-- -----------------------------------------------

select varargs(1,2);
 varargs
---------       3
(1 row)

-- -----------------------------------------------
-- verify the failure without arg defs

drop function varargs(int4 int4);
create function varargs() returns int4 as  '/tmp/varargs.so'  language 'C';

-- -----------------------------------------------

select varargs(1,2);

ERROR:  Function 'varargs(int4, int4)' does not exist        Unable to identify a function that satisfies the given
argument
 
types        You may need to add explicit typecasts

-- -----------------------------------------------


Christopher Kings-Lynne wrote:
>>If you're asking about whether a custom function can have vararg 
>>parameters, the answer appears to depend on the CREATE FUNCTION syntax. 
>>I've never used them personally, but the PG_FUNCTION_ARGS and 
>>PG_GETARG_xxx(#) macros (/src/includes/fmgr.h) available for compiled 
>>functions would appear to support variable length argument lists. The 
>>problem is that I couldn't pin down a CREATE FUNCTION that provided the 
>>same vararg functionality. Hopefully somebody can answer this 
>>conclusively.
> 
> 
> contrib/fulltextindex/fti.c uses variable numbers of arguments...
> 
> Chris
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

Marc Lavergne <mlavergne-pub@richlava.com> writes:
>>> contrib/fulltextindex/fti.c uses variable numbers of arguments...
> I see the code, but maybe I don't SEE the code. I'm only on my second 
> cup of coffee so I may be missing something but I am not betting any 
> money in it :) Fulltextindex appears to work because it's called within 
> a trigger but I don't think you can get the parser not to complain about 
> arguments when your function is not called internally by the trigger 
> manager.

Right, fti.c is using a variable number of *trigger* arguments, which
is a whole different can of worms.

What you can do, if you are so inclined, is to rely on function
overloading to make several pg_proc entries of the same name and
different numbers of arguments that all point at the same underlying
C function.  Then the C function would have to check how many
arguments it was actually passed.  Slightly ugly, but doable.

There is some stuff in fmgr.h that anticipates a future feature of
real varargs function declarations ... but we don't support it yet.
        regards, tom lane