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

Поиск
Список
Период
Сортировка
От He Weiping
Тема plpgsql's variable name can't be the same with table column?
Дата
Msg-id 3B7C9077.E7D99F7E@zhengmai.com.cn
обсуждение исходный текст
Список 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_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



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: crypt and null termination
Следующее
От: Doug McNaught
Дата:
Сообщение: Re: crypt and null termination