Re: Oracle Decode Function
От | Marc Lavergne |
---|---|
Тема | Re: Oracle Decode Function |
Дата | |
Msg-id | 3D405AE0.4010407@richlava.com обсуждение исходный текст |
Ответ на | Oracle Decode Function (ramirez@idconcepts.org (Edwin S. Ramirez)) |
Список | pgsql-hackers |
> 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 >
В списке pgsql-hackers по дате отправления: