Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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)

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group