pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPorting Oracle Decode to Postgresql
4+ messages / 2 participants
[nested] [flat]
* Porting Oracle Decode to Postgresql
@ 2001-11-22 08:13 MindTerm <mindterm@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: MindTerm @ 2001-11-22 08:13 UTC (permalink / raw)
To: pgsql-sql
Dear all,
I am writing to ask how to port oracle's decode( ..)
function to postgresql ?
It seems that plpgsql is not a possible way because
there are undefined input parameters for decode()
function. How about c ? any suggestion ? any existing
source ?
M.T.
__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Porting Oracle Decode to Postgresql
@ 2001-11-22 09:47 Tod McQuillin <devin@spamcop.net>
parent: MindTerm <mindterm@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tod McQuillin @ 2001-11-22 09:47 UTC (permalink / raw)
To: MindTerm <mindterm@yahoo.com>; +Cc: pgsql-sql
On Thu, 22 Nov 2001, MindTerm wrote:
> I am writing to ask how to port oracle's decode( ..)
> function to postgresql ?
The ANSI SQL version of decode is CASE.
See http://www.postgresql.org/idocs/index.php?functions-conditional.html
for the full documentation, but basically, it works like this:
Oracle:
decode (value, 0, 'zero', 1, 'one', 'unknown')
ANSI:
CASE WHEN value=0 THEN 'zero' WHEN value=1 THEN 'one' ELSE 'unknown' END
or
CASE value WHEN 0 THEN 'zero' WHEN 1 THEN 'one' ELSE 'unknown' END
--
Tod McQuillin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Porting Oracle Decode to Postgresql
@ 2001-11-22 09:54 MindTerm <mindterm@yahoo.com>
parent: Tod McQuillin <devin@spamcop.net>
0 siblings, 0 replies; 4+ messages in thread
From: MindTerm @ 2001-11-22 09:54 UTC (permalink / raw)
To: Tod McQuillin <devin@spamcop.net>; +Cc: pgsql-sql
Dear Tod McQuillin,
Can perform the action as same as oracle in
postgresql ?
e.g.
Select decode_function( ... , ... , ..... ) from ..
I can write a plpgsql to simulate the nvl function
by using coalesce .
CREATE FUNCTION nvl( numeric, numeric ) RETURNS
varchar AS '
DECLARE
input_value ALIAS FOR $1;
else_value ALIAS FOR $2;
output_value numeric;
BEGIN
select coalesce( input_value, else_value ) into
output_value ;
return output_value ;
END;
' LANGUAGE 'plpgsql' ;
M.T.
--- Tod McQuillin <devin@spamcop.net> wrote:
> On Thu, 22 Nov 2001, MindTerm wrote:
>
> > I am writing to ask how to port oracle's decode(
> ..)
> > function to postgresql ?
>
> The ANSI SQL version of decode is CASE.
>
> See
>
http://www.postgresql.org/idocs/index.php?functions-conditional.html
> for the full documentation, but basically, it works
> like this:
>
> Oracle:
> decode (value, 0, 'zero', 1, 'one', 'unknown')
>
> ANSI:
> CASE WHEN value=0 THEN 'zero' WHEN value=1 THEN
> 'one' ELSE 'unknown' END
>
> or
>
> CASE value WHEN 0 THEN 'zero' WHEN 1 THEN 'one' ELSE
> 'unknown' END
>
> --
> Tod McQuillin
>
>
__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Porting Oracle Decode to Postgresql
@ 2001-11-22 10:21 MindTerm <mindterm@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: MindTerm @ 2001-11-22 10:21 UTC (permalink / raw)
To: Michael Ansley \(UK\) <Michael.Ansley@intec-telecom-systems.com>; +Cc: pgsql-sql
Dear Michael Ansley ,
It seems that c program in postgresql support
unspecified number of input parameter. The example is
as following :
But in the CREATE FUNCTION , it needs to specify the
number and type of arguments... how to rewrite it to
support vary number of input parameters ?
c program:
PG_FUNCTION_INFO_V1(c_overpaid);
Datum
c_overpaid(PG_FUNCTION_ARGS)
{
TupleTableSlot *t = (TupleTableSlot *)
PG_GETARG_POINTER(0);
int32 limit = PG_GETARG_INT32(1);
bool isnull;
int32 salary;
salary = DatumGetInt32(GetAttributeByName(t,
"salary", &isnull));
if (isnull)
PG_RETURN_BOOL(false);
/* Alternatively, we might prefer to do
PG_RETURN_NULL() for null salary */
PG_RETURN_BOOL(salary > limit);
}
--- "Michael Ansley (UK)"
<Michael.Ansley@intec-telecom-systems.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Well, because you can overload functions, I guess
> that you could
> create a new decode for each number of parameters up
> to a limit. Is
> it likely that your program uses more than about
> eight or nine
> parameters? Then create the three or four overloads
> that it needs to
> get that many, and if you need more, then just
> create them when you
> do.
>
> Cheers...
>
>
> MikeA
__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-11-22 10:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-22 08:13 Porting Oracle Decode to Postgresql MindTerm <mindterm@yahoo.com>
2001-11-22 09:47 ` Tod McQuillin <devin@spamcop.net>
2001-11-22 09:54 ` MindTerm <mindterm@yahoo.com>
2001-11-22 10:21 Re: Porting Oracle Decode to Postgresql MindTerm <mindterm@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox