plpgsql's variable name can't be the same with table column?

From: He Weiping <laser(at)zhengmai(dot)com(dot)cn>
To: pgsql-hackers(at)postgresql(dot)org
Subject: plpgsql's variable name can't be the same with table column?
Date: 2001-08-17 03:33:12
Message-ID: 3B7C9077.E7D99F7E@zhengmai.com.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi, all
I've found a problem in pl/pgsql: the variable declared can't be the

same name of table's column name, here is a example:
-----------------------------------8<----------------

drop table userdata;
create table userdata (
userid text,
txnid text,
passwd text,
sdate timestamp,
edate timestamp,
amt numeric(12,2),
localtime timestamp
);
drop table logdata;
create table logdata (
userdata text
);
---------------------8<------------------
if I create a function & trigger like these:

-------------8<--------------------------
drop function parse_userdata();
create function parse_userdata() returns opaque as'
DECLARE
user_id text;
txn_id text;
pswd text;
ttt numeric;
amt numeric(12,2); --userdata.amt%TYPE; -- I can not use
numeric(12,2)
startdate timestamp;
crtime timestamp;
BEGIN
if length(new.userdata) < 33 then
raise exception ''userdata''''s length error'';
return new;
else
raise NOTICE ''it''''s a normal txn.'';
txn_id := substr(new.userdata, 14+19+1, 2);
raise notice ''txn_id is: %'', txn_id;
end if;
if txn_id = ''00'' then

raise notice ''it''''s login txn'';
user_id := substr(new.userdata, 14+1, 19);
pswd := substr(new.userdata, 14+19+1+2, 6);
INSERT INTO userdata
(userid, txnid, passwd, localtime)
VALUES
(user_id, txn_id, pswd,crtime);

else if txn_id =''01'' then
raise NOTICE ''it''''s a fix all in one inq
txn.'';
end if;
end if;
return new;
END; 'LANGUAGE 'plpgsql';
drop trigger log2userdata on logdata;
create trigger log2userdata after insert on logdata for each row
execute procedure parse_userdata();
-----------8<------------------

the creation went smoothly, but when I do a:

-------------8<--------------------------------------------
insert into logdata(userdata)
values('20000000000000456351010000019989700111111');
-------------8<--------------------------------------------

it reports:
laser_db=# insert into logdata(userdata)
values('20000000000000456351010000019989700111111');
NOTICE: plpgsql: ERROR during compile of parse_userdata near line 6
ERROR: parse error at or near "("

but if I change the definition to:
-----------------------8<------------------------------------
drop function parse_userdata();
create function parse_userdata() returns opaque as'
DECLARE
user_id text;
txn_id text;
pswd text;
ttt numeric;
amt userdata.amt%TYPE; -- I can not use numeric(12,2)
startdate timestamp;
crtime timestamp;
BEGIN
if length(new.userdata) < 33 then
raise exception ''userdata''''s length error'';
return new;
else
raise NOTICE ''it''''s a normal txn.'';
txn_id := substr(new.userdata, 14+19+1, 2);
raise notice ''txn_id is: %'', txn_id;
end if;

if txn_id = ''00'' then

raise notice ''it''''s login txn'';
user_id := substr(new.userdata, 14+1, 19);
pswd := substr(new.userdata, 14+19+1+2, 6);
INSERT INTO userdata
(userid, txnid, passwd, localtime)
VALUES
(user_id, txn_id, pswd,crtime);

else if txn_id =''01'' then
raise NOTICE ''it''''s a fix all in one inq
txn.'';
end if;
end if;
return new;
END; 'LANGUAGE 'plpgsql';
drop trigger log2userdata on logdata;
create trigger log2userdata after insert on logdata for each row
execute procedure parse_userdata();
-----------------------8<------------------------------------

then it' ok, and still another problem, if I declare the vairable pswd
to passwd
(same with userdata's column `paswd' name) then I'll get the error:

laser_db=# insert into logdata(userdata)
values('20000000000000456351010000019989700111111');
NOTICE: it's a normal txn.
NOTICE: txn_id is: 00
NOTICE: it's login txn
ERROR: parser: parse error at or near "$1"

I don't konw if it's reported, but I can't found any where in docs
mentioning these.
so I think at lease we should make it clear in docs, or, am I doing
something wrong?

regards laser

Browse pgsql-hackers by date

  From Date Subject
Next Message Karel Zak 2001-08-17 13:50:18 encoding names
Previous Message Doug McNaught 2001-08-17 03:17:16 Re: crypt and null termination