Thread: Oracle date type compat. functions: next_day, last_day, ..
Hello This patch is implementation of some date functions which produce better compatibility with Oracle (On request Radim Kolar). Major changes are in /src/backend/utils/adt/oracle_compat.c. There are new functions: next_day, last_day, add_months and months_between. Original Oracle doc http://www.techonthenet.com/oracle/functions/add_months.htm http://www.techonthenet.com/oracle/functions/last_day.htm http://www.techonthenet.com/oracle/functions/next_day.htm http://www.techonthenet.com/oracle/functions/months_between.htm Best regards Pavel Stehule
Attachment
Pavel Stehule wrote: > http://www.techonthenet.com/oracle/functions/add_months.htm > http://www.techonthenet.com/oracle/functions/last_day.htm > http://www.techonthenet.com/oracle/functions/next_day.htm > http://www.techonthenet.com/oracle/functions/months_between.htm Are these functions useful enough to be provided as builtins? add_months(), for example, seems to be a less general version of 'date' + 'interval'. > diff -c -r --new-file pgsql.old/src/include/catalog/pg_proc.h pgsql.new/src/include/catalog/pg_proc.h > *** pgsql.old/src/include/catalog/pg_proc.h 2005-05-20 03:29:55.000000000 +0200 > --- pgsql.new/src/include/catalog/pg_proc.h 2005-05-24 13:55:16.000000000 +0200 > *************** > *** 2195,2200 **** > --- 2195,2208 ---- > DESCR("trim selected characters from both ends of string"); > DATA(insert OID = 885 ( btrim PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_ btrim1 - _null_ )); > DESCR("trim spaces from both ends of string"); > + DATA(insert OID = 901 ( next_day PGNSP PGUID 12 f f t f i 2 1082 "1082 25" _null_ _null_ _null_ next_day - _null_)); > + DESCR("return the first weekday that is greater than a date value"); > + DATA(insert OID = 902 ( last_day PGNSP PGUID 12 f f t f i 1 1082 "1082" _null_ _null_ _null_ last_day - _null_)); > + DESCR("last day of the month"); > + DATA(insert OID = 903 ( months_between PGNSP PGUID 12 f f t f i 2 701 "1082 1082" _null_ _null_ _null_ months_between- _null_ )); > + DESCR("returns the number of months between date1 and date2"); > + DATA(insert OID = 904 ( add_months PGNSP PGUID 12 f f t f i 2 1082 "1082 23" _null_ _null_ _null_ add_months- _null_ )); > + DESCR("returns a date plus n months"); > > DATA(insert OID = 936 ( substring PGNSP PGUID 12 f f t f i 3 25 "25 23 23" _null_ _null_ _null_ text_substr -_null_ )); > DESCR("return portion of string"); These OIDs duplicate some OIDs used in pg_operator -- it is standard practice to assign OIDs to builtin system catalog rows that are unique across the _entire_ system catalogs, not just the particular catalog where the row exists. You can use the include/catalog/unused_oids script to find some candidate OIDs. -Neil
On Fri, 3 Jun 2005, Neil Conway wrote: > Pavel Stehule wrote: > > http://www.techonthenet.com/oracle/functions/add_months.htm > > http://www.techonthenet.com/oracle/functions/last_day.htm > > http://www.techonthenet.com/oracle/functions/next_day.htm > > http://www.techonthenet.com/oracle/functions/months_between.htm > > Are these functions useful enough to be provided as builtins? > add_months(), for example, seems to be a less general version of 'date' > + 'interval'. it's 50%/50%. I wrote these functions from initiative Oracle's users. There is not too much work write similar PL/pgSQL functions, but there is "kind of people" which can't to do. Sometimes isn't possible modify SQL. I have list of missing functions and on the top are these functions. In my opinion add_months and months_between are usefull only for porting Oracle application (but creating contrib for only two functions?). last_day and next_day are usefull. I am sure. I can't to see haw is importance of simplicity of porting from Oracle, but PostgreSQL has some Oracles functions. I am sure so PostgreSQL can be more attractive (I don't prefere extravagance of EnterepriseDB - we lost too much good funcionality), but it's not honest argument. My next patch is implementation least and greatest functions. If will possible I prefere contrib for it, but it's inpossible. I had to modify parser. I know so there exists workaround of its, but it's really ugly. One year ago was debate here about some compatibility layer. It's shoul be nice, but it's music of future, unfortunately. I belive so this funcionality can be help for enough people. > > > diff -c -r --new-file pgsql.old/src/include/catalog/pg_proc.h pgsql.new/src/include/catalog/pg_proc.h > > *** pgsql.old/src/include/catalog/pg_proc.h 2005-05-20 03:29:55.000000000 +0200 > > --- pgsql.new/src/include/catalog/pg_proc.h 2005-05-24 13:55:16.000000000 +0200 > > *************** > > *** 2195,2200 **** > > --- 2195,2208 ---- > > DESCR("trim selected characters from both ends of string"); > > DATA(insert OID = 885 ( btrim PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_ btrim1 - _null_)); > > DESCR("trim spaces from both ends of string"); > > + DATA(insert OID = 901 ( next_day PGNSP PGUID 12 f f t f i 2 1082 "1082 25" _null_ _null_ _null_ next_day -_null_ )); > > + DESCR("return the first weekday that is greater than a date value"); > > + DATA(insert OID = 902 ( last_day PGNSP PGUID 12 f f t f i 1 1082 "1082" _null_ _null_ _null_ last_day - _null_)); > > + DESCR("last day of the month"); > > + DATA(insert OID = 903 ( months_between PGNSP PGUID 12 f f t f i 2 701 "1082 1082" _null_ _null_ _null_ months_between- _null_ )); > > + DESCR("returns the number of months between date1 and date2"); > > + DATA(insert OID = 904 ( add_months PGNSP PGUID 12 f f t f i 2 1082 "1082 23" _null_ _null_ _null_ add_months- _null_ )); > > + DESCR("returns a date plus n months"); > > > > DATA(insert OID = 936 ( substring PGNSP PGUID 12 f f t f i 3 25 "25 23 23" _null_ _null_ _null_ text_substr- _null_ )); > > DESCR("return portion of string"); > > These OIDs duplicate some OIDs used in pg_operator -- it is standard > practice to assign OIDs to builtin system catalog rows that are unique > across the _entire_ system catalogs, not just the particular catalog > where the row exists. You can use the include/catalog/unused_oids script > to find some candidate OIDs. I didn't know about this script. I can change it, or you. thank you Pavel > > -Neil >
Pavel Stehule wrote: > On Fri, 3 Jun 2005, Neil Conway wrote: > > >>Pavel Stehule wrote: >> >>>http://www.techonthenet.com/oracle/functions/add_months.htm >>>http://www.techonthenet.com/oracle/functions/last_day.htm >>>http://www.techonthenet.com/oracle/functions/next_day.htm >>>http://www.techonthenet.com/oracle/functions/months_between.htm >> >>Are these functions useful enough to be provided as builtins? >>add_months(), for example, seems to be a less general version of 'date' >>+ 'interval'. Why not a pgFoundry project called Oracle-Compat or something? There are plenty of functions etc... that can be included in the package as a whole. Once it is large enough, push it to contrib or vie for core support. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
On Thu, Jun 02, 2005 at 09:17:07AM -0700, Joshua D. Drake wrote: > Pavel Stehule wrote: > >> > >>Are these functions useful enough to be provided as builtins? > >>add_months(), for example, seems to be a less general version of > >>'date' + 'interval'. > > Why not a pgFoundry project called Oracle-Compat or something? There > are plenty of functions etc... that can be included in the package > as a whole. > > Once it is large enough, push it to contrib or vie for core support. Pavel created one (http://pgfoundry.org/projects/orafce/) and I'm the one who encouraged him to take the stuff directly to -hackers :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Thu, Jun 02, 2005 at 05:41:46PM +0200, Pavel Stehule wrote: > My next patch is implementation least and greatest functions. If > will possible I prefere contrib for it, but it's inpossible. I had > to modify parser. I know so there exists workaround of its, but it's > really ugly. BTW, the thing about least() and greatest() (basically the row-wise versions of min() and max()) has come up before, and they would be quite handy :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
> > Why not a pgFoundry project called Oracle-Compat or something? There > are plenty of functions etc... that can be included in the package as a > whole. > > Once it is large enough, push it to contrib or vie for core support. > Is exists. Not in very usefull state, true. http://pgfoundry.org/projects/orafce/ I have two motivations for commit in main code. I don't speak about functions like add_months (it's can be in sontrib, for me). a) next_day, last_day are usefull, I am sure, and I wont to use them. b) some functions need patch to parser - greatest, least and decode. I don't know way to implemented its efective like normal contrib functions. And if they are outside main tree, you have to maintain patch :-(. I expect so less used functions will go to contrib: LNNVL, .. but some functions can by general usefull. There is WIDTH_BUCKET in core too now. Who use it? Pavel Stehule
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> b) some functions need patch to parser - greatest, least and decode.
Why?
regards, tom lane
On Thu, 2 Jun 2005, Tom Lane wrote:
> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> > b) some functions need patch to parser - greatest, least and decode.
>
> Why?
>
they has variable number of argument, they are polymorphic. They are not
functions, more special form of operators.
Maybe it is possible. I don't know PostgreSQL internal structures best,
but I can't to write similar functions without change parser.
Is it possible? I didn't find any example.
And code
for greatest and least is easy now, no more than 100 lines of code. Decode
is more compliceted (like case operator). Least and greatest use equal
functions and decode only different last ExecEvalVarargDecode. And calling
of these functions are much cheeper than calling true function.
Pavel Stehule
the change of parser is minimalistic:
| COALESCE '(' expr_list ')'
{
CoalesceExpr *c =
makeNode(CoalesceExpr);
c->args = $3;
$$ = (Node *)c;
}
| GREATEST '(' expr_list ')'
{
VarargExpr *v =
makeNode(VarargExpr);
v->args = $3;
v->type = IS_GREATEST;
$$ = (Node *)v;
}
| LEAST '(' expr_list ')'
{
VarargExpr *v =
makeNode(VarargExpr);
v->args = $3;
v->type = IS_LEAST;
$$ = (Node *)v;
}
| DECODE '(' expr_list ')'
{
VarargExpr *v =
makeNode(VarargExpr);
v->args = $3;
v->type = IS_DECODE;
$$ = (Node *)v;
}
On Thu, 2005-06-02 at 18:53 +0200, Pavel Stehule wrote: > I expect so less used functions will go to contrib: LNNVL, .. but some > functions can by general usefull. There is WIDTH_BUCKET in core too now. > Who use it? width_bucket() is defined by SQL2003, and is included largely for compliance with the spec. -Neil
> I expect so less used functions will go to contrib: LNNVL, .. but some > functions can by general usefull. There is WIDTH_BUCKET in core too now. > Who use it? Width_bucket is in the sql standard...
David Fetter wrote: > On Thu, Jun 02, 2005 at 05:41:46PM +0200, Pavel Stehule wrote: >>My next patch is implementation least and greatest functions. If >>will possible I prefere contrib for it, but it's inpossible. ... > > BTW, the thing about least() and greatest() (basically the row-wise > versions of min() and max()) has come up before, and they would be > quite handy :) At least for integers, I think I've done something similar using the intarray contrib; like this... select (sort_desc(array[1,2,3])) [1]; -- for greatest select (sort(array[2,1,3])) [1]; -- for least (if I understand right what the greatest function does in O*). For other types something similar should work, but I guess the sort function would need to be written.