Re: Oracle date type compat. functions: next_day, last_day,

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
>