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

[vinita bansal]

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.
[url]http://creative.mediaturf.net/creatives/icicibank/june/kodak/OTP.htm[/url] And win
a FREE ticket to India.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[John DeSoi]

On Dec 27, 2004, at 11:36 AM, vinita bansal wrote:

> 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;
>

for1 is a record type from which you can access the other columns. So
with something like

FOR for1 in select * from cm_quotainstance where ... loop

then you can access the columns in your subsequent if statements like:

if (for1.startdate > > M_COMM_CLOSE_DATE OR for1.enddate <=
M_COMM_CLOSE_DATE) ...

Look in the plpgsql section of the documentation under "Looping Through
Query Results" -- this is section 36.7.4 in the 8.0 documentation.


Best,

John DeSoi, Ph.D.
[url]http://pgedit.com/[/url]
Power Tools for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])