Обсуждение: Help with simple function

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)

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)

On 12/28/05, Jason Minion <jason.minion@sigler.com> wrote: