error : syntax error at or near $1 for over select rows

From: "vinita bansal" <sagivini(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: error : syntax error at or near $1 for over select rows
Date: 2004-12-27 16:36:21
Message-ID: BAY20-F31E72B46AB4F60EE23DFE1CB990@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


This is the error i am getting when calling select * from
cas_reset_qi_changedate('CAS','2003-02-03'

ERROR: syntax error at or near "$1" at character 25
CONTEXT: PL/pgSQL function "cas_reset_qi_changedate" line 15 at for over
select rows

pgsql function :
CREATE or replace FUNCTION qe13.CAS_RESET_QI_CHANGEDATE (VARCHAR,TIMESTAMP)
RETURNS INTEGER AS '

DECLARE
AGR_UNID ALIAS for $1;
M_COMM_CLOSE_DATE ALIAS for $2;
DEFAULT_LOWEST_DATE timestamp default ''1900-01-01 00:00:00.0'';
vQuotaInstanceGID varchar(34) default null;
vQIStartDate timestamp;
vQIEndDate timestamp;
for1 record;

BEGIN

FOR for1 in select qi.tril_gid as vQuotaInstanceGID,qi.startdate as
vQIStartDate,qi.enddate as vQIEndDate from cm_quotainstance as qi, cm_quota
as q, fs_agr as a where a.fs_unid = AGR_UNID and a.fs_model = q.model and
qi.quota = q.tril_gid LOOP

if (vQIStartDate > M_COMM_CLOSE_DATE OR vQIEndDate
<= M_COMM_CLOSE_DATE) then
update cm_quotainstance set changedate =
DEFAULT_LOWEST_DATE where tril_gid = vQuotaInstanceGID;
end if;
END LOOP;

RETURN 0;

END;
' LANGUAGE 'plpgsql';

I know the problem is that we cant use a variable name in a select query.
But then how can I use this variable later in "if" statement (say
vQIStartDate variable)
Thanks and Regards,
Vinita Bansal

_________________________________________________________________
NRIs send 10 photos FREE to India.
http://creative.mediaturf.net/creatives/icicibank/june/kodak/OTP.htm And win
a FREE ticket to India.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dan Boitnott 2004-12-27 16:39:48 Large Objects
Previous Message Michael Fuhr 2004-12-27 14:54:06 Re: Get current trasanction id