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


Fri, 17 Aug 2001 11:33:12 +0800
He Weiping <laser@zhengmai.com.cn>
plpgsql's variable name can't be the same with table column?