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

Re: Help with simple function

From: "Jason Minion" <jason(dot)minion(at)sigler(dot)com>
To: "Chris Hoover" <revoohc(at)gmail(dot)com>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Help with simple function
Date: 2005-12-28 15:11:10
Message-ID: 0105A1BF505D304E9E5AF38B63E40E4ED7E895@EXCHANGE.siglercompanies.com (view raw or flat)
Thread:
Lists: 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(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Chris Hoover
Sent: Wednesday, December 28, 2005 8:49 AM
To: pgsql-admin(at)postgresql(dot)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)



Responses

pgsql-admin by date

Next:From: Bradley KieserDate: 2005-12-28 15:12:23
Subject: Re: migrating oracle table to PostgresQL
Previous:From: Mohamed FazilDate: 2005-12-28 15:04:47
Subject: Re: How to do Client Only Installation on Windows as a service?

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