X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E68089DC9DD for ; Wed, 28 Dec 2005 10:49:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82786-05 for ; Wed, 28 Dec 2005 10:49:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.201]) by postgresql.org (Postfix) with ESMTP id 794729DC9D7 for ; Wed, 28 Dec 2005 10:49:03 -0400 (AST) Received: by xproxy.gmail.com with SMTP id s14so990982wxc for ; Wed, 28 Dec 2005 06:49:08 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=haTM/2xp/a4vCLJ3MQkzH85XTDgkwoiAIaHjQSnC8u0qG/mHqkF1DWjtuq7BHiVyPjU2tKsj0/VJ5bV6sD7GNLh6STt23QAmASVwiYz5cbjHR9U/Cd34A9OvDpCJTrgfc+pciLQOtZb2sTeqcJxrYvWSaskbs9yPCzobG15Y6RI= Received: by 10.70.15.6 with SMTP id 6mr7783852wxo; Wed, 28 Dec 2005 06:49:08 -0800 (PST) Received: by 10.70.84.7 with HTTP; Wed, 28 Dec 2005 06:49:08 -0800 (PST) Message-ID: <1d219a6f0512280649n3970fd99kbe6fb57f9dc7d102@mail.gmail.com> Date: Wed, 28 Dec 2005 09:49:08 -0500 From: Chris Hoover To: "pgsql-admin@postgresql.org" Subject: Help with simple function MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_46406_18343556.1135781348130" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.04 required=5 tests=[AWL=0.039, HTML_MESSAGE=0.001] X-Spam-Score: 0.04 X-Spam-Level: X-Archive-Number: 200512/339 X-Sequence-Number: 20059 ------=_Part_46406_18343556.1135781348130 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I need some help. I am trying to get a very simple function written to hel= p 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) ------=_Part_46406_18343556.1135781348130 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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, i= nteger) 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)
------=_Part_46406_18343556.1135781348130--