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: (view raw or whole thread)
Lists: pgsql-admin
Instead of:
interval "numDays day"
(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!



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'
       startDate alias for $1;
       numDays alias for $2;
       retDate varchar(8);
  select to_char((startDate - interval ''numDays day''),''yyyymmdd'')
into retDate;
  return retDate;

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,


PG7.3.4 (8.1.1 upgrade happening next month)


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-2015 The PostgreSQL Global Development Group