postgresql9.4 函数无法捕捉异常错误, 这到底是那里错了?

狗操_我今年大三它妈 发布于 2015/05/21 11:13
阅读 1K+
收藏 0

写了一个函数,如下:

            CREATE TABLE account_status
            (
              uname character varying(255) NOT NULL ,
              is_login boolean NOT NULL DEFAULT false,
              last_login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              chost character varying(22) NOT NULL DEFAULT '',
              CONSTRAINT account_status_uname_fkey FOREIGN KEY (uname)
                  REFERENCES account (uname) MATCH SIMPLE
                  ON UPDATE NO ACTION ON DELETE NO ACTION
            );
            
            CREATE OR REPLACE FUNCTION update_or_insert_table(name text,host text) RETURNS VOID AS
            $$
            BEGIN
                UPDATE account_status SET last_login_time = NOW(),chost = host WHERE uname = name;
            EXCEPTION WHEN internal_error THEN
                INSERT INTO account_status(uname,is_login,last_login_time,chost) VALUES(name,True,NOW(),host);
            END;
            $$
            LANGUAGE plpgsql;

但是死活都报如下的ERROR,不能catch 到这种异常。

以下是日志中的错误:

2015-05-21 11:05:26 CST [29014-9] postgres@nath ERROR:  column "5e0b0897a3954ad7b497816fdf64dfd400000000b7e7b83d" does not exist at character 31
2015-05-21 11:05:26 CST [29014-10] postgres@nath STATEMENT:  select update_or_insert_table("5e0b0897a3954ad7b497816fdf64dfd400000000b7e7b83d","192.168.25.101:64972");

请同学指教一二,谢谢。

加载中
0
huan
huan
注意引号双引号
0
狗操_我今年大三它妈
狗操_我今年大三它妈

这里是postgresql 9.4的参考文档里复制过来的,照样还是报上面的错误。

        CREATE TABLE account_status

            (
              uname character varying(255) NOT NULL ,
              is_login boolean NOT NULL DEFAULT false,
              last_login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
              chost character varying(22) NOT NULL DEFAULT '',
              CONSTRAINT account_status_uname_fkey FOREIGN KEY (uname)
                  REFERENCES account (uname) MATCH SIMPLE
                  ON UPDATE NO ACTION ON DELETE NO ACTION
            );
            
            CREATE OR REPLACE FUNCTION update_or_insert_table(name text,host text) RETURNS VOID AS
            $$
            BEGIN
                LOOP
                    UPDATE account_status SET last_login_time = NOW(),chost = host WHERE uname = name;
                    if found THEN
                        RETURN;
                    END IF;
                    BEGIN
                        INSERT INTO account_status(uname,is_login,last_login_time,chost) VALUES(name,True,NOW(),host);
                        RETURN;
                    EXCEPTION WHEN unique_violation THEN
                    END;
                END LOOP;
            END;
            $$
            LANGUAGE plpgsql;

返回顶部
顶部