Re: Oracle date type compat. functions: next_day, last_day, - Mailing list pgsql-patches

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
>