Thread: Re: How to implement GOMONTH function

Andrus ha escrito:
> I need to create function GOMONTH which returns date by given number of
> month before or forward using sql or pgsql in 8.1+
> For example,
> GOMONTH( DATE '20070513', 1 )  should return date '20070613'
> GOMONTH( DATE '20070513', -2 )  should return date '20070313'
>
> I tried
>
> CREATE OR REPLACE FUNCTION public.gomonth(date, integer,
>         out date) IMMUTABLE AS
> $_$
> SELECT $1 + $2'months';
> $_$ language sql
>
> but got error
>
> ERROR: syntax error at or near "'months'"
>
> How to implement this ?
>
> Andrus.

CREATE OR REPLACE FUNCTION
PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE AS
$_$
SELECT ($1 + ($2 || 'MONTHS')::INTERVAL)::DATE;
$_$ LANGUAGE SQL

On Sun, May 13, 2007 at 02:26:09PM -0700, Rodrigo De León wrote:
> CREATE OR REPLACE FUNCTION
> PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE AS
> $_$
> SELECT ($1 + ($2 || 'MONTHS')::INTERVAL)::DATE;
> $_$ LANGUAGE SQL

It would probably be better to use:

SELECT ($1 + ($2 * '1 MONTH'::INTERVAL))::DATE;

Less string parsing.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Thank all very much for great suggestions.

I created function

CREATE OR REPLACE FUNCTION PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE
AS
$_$
SELECT ($1 + ($2 * '1 MONTH'::INTERVAL))::DATE;
$_$ LANGUAGE SQL;

I got errors:

function gomonth(date, numeric) does not exist

and

function gomonth(date, bigint ) does not exist

How to fix those errors ?


Andrus.

Andrus wrote:
> Thank all very much for great suggestions.
>
> I created function
>
> CREATE OR REPLACE FUNCTION PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE)
> IMMUTABLE
> AS
> $_$
> SELECT ($1 + ($2 * '1 MONTH'::INTERVAL))::DATE;
> $_$ LANGUAGE SQL;
>
> I got errors:
>
> function gomonth(date, numeric) does not exist

Why would you want to call the function with a numeric? What does 1.2
months mean to you? You're probably only interested in the integer part
of the numeric.

> function gomonth(date, bigint ) does not exist

Do you really expect to calculate dates over 2 billion months in the
future or the past?

If you really want to; you can write gomonth versions for numeric and
bigint month counts with a body that casts the months value to integer
and calls the gomonth(date, integer) version.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Alban,

> Why would you want to call the function with a numeric? What does 1.2
> months mean to you? You're probably only interested in the integer part
> of the numeric.

create table test ( m numeric(2) );
select gomonth( current_date, m ) from test;

ERROR: function gomonth(date, numeric) does not exist

Do you think it is bad practice to use the table structure above ?
Do you think that I must use integer instead of numeric(2) and change all my
table structures?

Why numeric(2) is not casted to integer automatically ?

>> function gomonth(date, bigint ) does not exist
>
> Do you really expect to calculate dates over 2 billion months in the
> future or the past?

I really do not want. PostgeSQL wants it.

create table test ( t integer  );
select gomonth( current_date, sum(t) ) from test;

ERROR: function gomonth(date, bigint) does not exist

> If you really want to; you can write gomonth versions for numeric and
> bigint month counts with a body that casts the months value to integer
> and calls the gomonth(date, integer) version.

I really want to write only single function version.

Andrus.

"Andrus" <kobruleht2@hot.ee> writes:
> Why numeric(2) is not casted to integer automatically ?

Because it would lose data, eg '4.4' being rounded to 4.

            regards, tom lane

>> Why numeric(2) is not casted to integer automatically ?
>
> Because it would lose data, eg '4.4' being rounded to 4.

create temp table test ( test numeric(2));
insert into test values (0.5);
select * from test

returns

1


I'm really confused now.

I tought that numeric(2) can store only integer data, without decimal
points.

Can you give example how   numeric(2) -> integer conversion can  lose data,
please.


Andrus.

"Andrus" <kobruleht2@hot.ee> writes:
>>> Why numeric(2) is not casted to integer automatically ?
>>
>> Because it would lose data, eg '4.4' being rounded to 4.

> I tought that numeric(2) can store only integer data, without decimal
> points.

Oh, I see your confusion: you're supposing that we might make different
casting decisions about numeric(2) than, say, numeric(2,1).  Sorry,
it doesn't work that way.  The base data type is all that is used
to determine the applicability of casts (or any other function).

            regards, tom lane