Help with simple function

From: Chris Hoover <revoohc(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Help with simple function
Date: 2005-12-28 14:49:08
Message-ID: 1d219a6f0512280649n3970fd99kbe6fb57f9dc7d102@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Browse pgsql-admin by date

  From Date Subject
Next Message Gary Stainburn 2005-12-28 14:53:01 restored database locale problem
Previous Message Murugan G 2005-12-28 14:34:54 migrating oracle table to PostgresQL