Received: from localhost (unknown [200.46.204.184])
	by developer.postgresql.org (Postfix) with ESMTP id 13BB02E0080
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Thu,  3 Apr 2008 07:25:03 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 47488-03
	for <pgsql-sql-postgresql.org@developer.postgresql.org>;
	Thu,  3 Apr 2008 07:24:53 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from mu-out-0910.google.com (mu-out-0910.google.com
 [209.85.134.189])
	by developer.postgresql.org (Postfix) with ESMTP id B06772E0062
	for <pgsql-sql@postgresql.org>; Thu,  3 Apr 2008 07:24:58 -0300 (ADT)
Received: by mu-out-0910.google.com with SMTP id i10so4645836mue.5
	for <pgsql-sql@postgresql.org>; Thu, 03 Apr 2008 03:24:57 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type;
	bh=2JA4yOFELSSdY0Acuun9avwlZEZWyFdkCalz72R003E=;
	b=JSrOPOvOu81nVWOk6oH76bX1eYdU+wWoe+mn/ipgb7taI2pwWNPRv0E43nKveHo7GIWFGrZ4qMXcBVYwbeKELhNYvRfSFKtcw012kKbK66OY1QWxjP0PYQvLALhrlXjA7L7tGyUXL8y6iRiUP8syJ0Tgac8S6eACSBOa+FmzBS0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=tA9N7vwc28ceToSyJaJ2EgHlzM1P+vJXp1ETwBmljgVr9gkAEBIyimxuoQwyllYd0ioUlsdzJZMg/vVnTSPoH8NrnKPgWmDEfsB/bunnrreHtDfcF5aCI1kgPMKDHL8mnCNvsO++09aVRKoAwtDLdms8tjbofN2j4sOtusWt56Y=
Received: by 10.78.122.2 with SMTP id u2mr3388176huc.38.1207218296841;
	Thu, 03 Apr 2008 03:24:56 -0700 (PDT)
Received: by 10.78.118.8 with HTTP; Thu, 3 Apr 2008 03:24:56 -0700 (PDT)
Message-ID: <1a027d210804030324x263be097x8be6f2fea6ca183d@mail.gmail.com>
Date: Thu, 3 Apr 2008 15:54:56 +0530
From: "Anoop G" <anoopmadavoor@gmail.com>
To: pgsql-sql@postgresql.org
Subject: BROBLEM IN BETWEEN QUERY (plpgsql)
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_641_9553234.1207218296825"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200804/18
X-Sequence-Number: 30557

------=_Part_641_9553234.1207218296825
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

hai all,

    I have  a  plpgsql function and I am using   postgresl 8.1.9


CREATE OR REPLACE FUNCTION get_vehicle_id(INT) RETURNS SETOF RECORD AS
$BODY$
DECLARE

    r RECORD;
        int_day  ALIAS FOR $1;

BEGIN

     FOR r in SELECT fk_bint_old_vehicle_number AS vehicle_id
     FROM tbl_rac_vehicle_replacement
     WHERE dat_replacement BETWEEN now() - interval '% day',int_day
     AND now() LOOP

             RETURN NEXT r;
     END LOOP;

     RETURN;
END
$BODY$ LANGUAGE 'plpgsql';


   But i cant craete the function  The error message is:


ERROR:  syntax error at or near "," at character 137
QUERY:   SELECT fk_bint_old_vehicle_number AS vehicle_id FROM
tbl_rac_vehicle_replacement WHERE dat_replacement BETWEEN now() - interval
'% day', $1  AND now()
CONTEXT:  SQL statement in PL/PgSQL function "test" near line 11
LINE 1: ...E dat_replacement BETWEEN now() - interval '% day', $1  AND ...




The problem line is  BETWEEN now() - interval '% day', $1  AND now()

I want to select  dat_replacement  between now ()  and now - 5 dyas or now
-7 days like that  i want to pass the integer value as argument to the
function.


I also tried like this  WHERE dat_replacement BETWEEN now() - interval
int_day day
     AND now(),but it also failed.


     Please help me .

  regards:
              Anoop

------=_Part_641_9553234.1207218296825
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

hai all,<br><br>&nbsp;&nbsp;&nbsp; I have&nbsp; a&nbsp; plpgsql function and I am using&nbsp;&nbsp; postgresl 8.1.9<br><br><br>CREATE OR REPLACE FUNCTION get_vehicle_id(INT) RETURNS SETOF RECORD AS $BODY$<br>DECLARE<br><br>&nbsp;&nbsp;&nbsp; r RECORD;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; int_day&nbsp; ALIAS FOR $1;<br>
<br>BEGIN<br><br>&nbsp;&nbsp;&nbsp;&nbsp; FOR r in SELECT fk_bint_old_vehicle_number AS vehicle_id<br>&nbsp;&nbsp;&nbsp;&nbsp; FROM tbl_rac_vehicle_replacement<br>&nbsp;&nbsp;&nbsp;&nbsp; WHERE dat_replacement BETWEEN now() - interval &#39;% day&#39;,int_day <br>&nbsp;&nbsp;&nbsp;&nbsp; AND now() LOOP<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN NEXT r;<br>&nbsp;&nbsp;&nbsp;&nbsp; END LOOP;<br>&nbsp;&nbsp;&nbsp;&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp; RETURN;<br>END<br>$BODY$ LANGUAGE &#39;plpgsql&#39;;<br><br><br>&nbsp;&nbsp; But i cant craete the function&nbsp; The error message is:<br><br><br>ERROR:&nbsp; syntax error at or near &quot;,&quot; at character 137<br>
QUERY:&nbsp;&nbsp; SELECT fk_bint_old_vehicle_number AS vehicle_id FROM tbl_rac_vehicle_replacement WHERE dat_replacement BETWEEN now() - interval &#39;% day&#39;, $1&nbsp; AND now()<br>CONTEXT:&nbsp; SQL statement in PL/PgSQL function &quot;test&quot; near line 11<br>
LINE 1: ...E dat_replacement BETWEEN now() - interval &#39;% day&#39;, $1&nbsp; AND ...<br><br><br><br><br>The problem line is&nbsp;  BETWEEN now() - interval &#39;% day&#39;, $1&nbsp; AND now()<br><br>I want to select&nbsp; dat_replacement&nbsp; between now ()&nbsp; and now - 5 dyas or now -7 days like that&nbsp; i want to pass the integer value as argument to the function.<br>
<br><br>I also tried like this&nbsp; WHERE dat_replacement BETWEEN now() - interval int_day day <br>&nbsp;&nbsp;&nbsp;&nbsp; AND now(),but it also failed.<br><br><br>&nbsp;&nbsp;&nbsp;&nbsp; Please help me .<br><br>&nbsp; regards:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Anoop<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
<br><br><br><br><br>&nbsp;&nbsp;&nbsp; <br><br>&nbsp;&nbsp; <br><br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>

------=_Part_641_9553234.1207218296825--