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/

On Sun, 13 May 2007, Andrus wrote:

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'

Andrus,

I do not know the context for which you need this, but you can use SQL to
calculate DATE + INTERVAL. For example, you could include in a SELECT
statement

WHERE CURRENT_DATE BETWEEN (DATE '20070513' - INTERVAL '2 month') AND
(DATE '20070513' + INTERVAL '1 month')

The PostgreSQL docs have an excellent section on temporal data types.
Also, search Google with the string 'Richard T. Snodgras' (at the University
of Arizona) and download the pdf copy of his book, 'Developing Time-Oriented
Database Applications in SQL.' I learned of this book just this morning.

HTH,

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863

Andrus wrote:

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';

This should read:

$3 := $1 + $2 * INTERVAL '1 month';

I'm not entirely sure about $3, I've never used out parameters before.

$_$ language sql

but got error

ERROR: syntax error at or near "'months'"

How to implement this ?

Andrus.

--
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 //

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