pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with simple function
3+ messages / 2 participants
[nested] [flat]
* Help with simple function
@ 2005-12-28 14:49 Chris Hoover <revoohc@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Chris Hoover @ 2005-12-28 14:49 UTC (permalink / raw)
To: pgsql-admin
I need some help. I am trying to get a very simple function written to help
our developers. Unfortunetly, the language we are developing in is giving
us some grief over trying to do a "select to_char((current_date - inteval '1
day'),'yyyymmdd') into x". It is complaining about the use of the work
interval.
Anyway, I thought, no problem, I'll just create a date increment and date
decrement set of functions. However, I can not get them to work. Can
someone please point out what is wrong?
CREATE OR REPLACE FUNCTION "public"."date_dec" (date, integer) RETURNS
varchar AS'
declare
startDate alias for $1;
numDays alias for $2;
retDate varchar(8);
begin
select to_char((startDate - interval ''numDays day''),''yyyymmdd'') into
retDate;
return retDate;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
When I try to run this, it complains about numDays - "Error: Bad interval
external representation 'numDays day'". Is there anyway around this? I am
trying to keep the function generic so that our developers can use it to
calculate any date range.
Thanks for any help,
Chris
PG7.3.4 (8.1.1 upgrade happening next month)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with simple function
@ 2005-12-28 15:11 Jason Minion <jason.minion@sigler.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jason Minion @ 2005-12-28 15:11 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; pgsql-admin
Instead of:
interval "numDays day"
use:
(numDays * (interval '1 day'))
Also, note that in your "select to_char...." you have misspelled (sp?)
interval as "inteval".
And last but not least, you can do simple day addition and subtraction
with dates using integers. For example:
> select '2005-12-26'::date + 8;
returns '2006-01-03'. However, I'm not sure if that will work with
7.3.x. Good luck with your 8.x install!
Jason
________________________________
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris Hoover
Sent: Wednesday, December 28, 2005 8:49 AM
To: pgsql-admin@postgresql.org
Subject: [SPAM] - [ADMIN] Help with simple function - Bayesian Filter
detected spam
I need some help. I am trying to get a very simple function written to
help our developers. Unfortunetly, the language we are developing in is
giving us some grief over trying to do a "select to_char((current_date -
inteval '1 day'),'yyyymmdd') into x". It is complaining about the use
of the work interval.
Anyway, I thought, no problem, I'll just create a date increment and
date decrement set of functions. However, I can not get them to work.
Can someone please point out what is wrong?
CREATE OR REPLACE FUNCTION "public"."date_dec" (date, integer) RETURNS
varchar AS'
declare
startDate alias for $1;
numDays alias for $2;
retDate varchar(8);
begin
select to_char((startDate - interval ''numDays day''),''yyyymmdd'')
into retDate;
return retDate;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
When I try to run this, it complains about numDays - "Error: Bad
interval external representation 'numDays day'". Is there anyway around
this? I am trying to keep the function generic so that our developers
can use it to calculate any date range.
Thanks for any help,
Chris
PG7.3.4 (8.1.1 upgrade happening next month)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with simple function
@ 2005-12-28 15:33 Chris Hoover <revoohc@gmail.com>
parent: Jason Minion <jason.minion@sigler.com>
0 siblings, 0 replies; 3+ messages in thread
From: Chris Hoover @ 2005-12-28 15:33 UTC (permalink / raw)
To: Jason Minion <jason.minion@sigler.com>; +Cc: pgsql-admin
On 12/28/05, Jason Minion <jason.minion@sigler.com> wrote:
>
> And last but not least, you can do simple day addition and subtraction
> with dates using integers. For example:
>
> > select '2005-12-26'::date + 8;
>
> returns '2006-01-03'. However, I'm not sure if that will work with
> 7.3.x. Good luck with your 8.x install!
>
> Jason
That works like a charm. Thanks a bunch for the help.
Chris
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-28 15:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-28 14:49 Help with simple function Chris Hoover <revoohc@gmail.com>
2005-12-28 15:11 Re: Help with simple function Jason Minion <jason.minion@sigler.com>
2005-12-28 15:33 ` Chris Hoover <revoohc@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox