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_idis: %'', 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
