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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
