PostgreSQL 异常处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4877637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Exception Handling
提问by Nick Binnet
I am new to PostgreSQL. Could anybody please correct this query.
我是 PostgreSQL 的新手。任何人都可以请更正此查询。
BEGIN TRANSACTION;
BEGIN;
CREATE TABLE "Logs"."Events"
(
EventId BIGSERIAL NOT NULL PRIMARY KEY,
PrimaryKeyId bigint NOT NULL,
EventDateTime date NOT NULL DEFAULT(now()),
Action varchar(12) NOT NULL,
UserId integer NOT NULL REFERENCES "Office"."Users"(UserId),
PrincipalUserId varchar(50) NOT NULL DEFAULT(user)
);
CREATE TABLE "Logs"."EventDetails"
(
EventDetailId BIGSERIAL NOT NULL PRIMARY KEY,
EventId bigint NOT NULL REFERENCES "Logs"."Events"(EventId),
Resource varchar(64) NOT NULL,
OldVal varchar(4000) NOT NULL,
NewVal varchar(4000) NOT NULL
);
COMMIT TRANSACTION;
RAISE NOTICE 'Task completed sucessfully.'
EXCEPTION;
ROLLBACK TRANSACTION;
RAISE ERROR @ErrorMessage, @LineNumber, @ErrorState --how to catch errors and display them????
END;
Questions:
问题：
- How to print a message like 'PRINT' in T-SQL?
- How to raise errors with exception information?
- 如何在 T-SQL 中打印像“PRINT”这样的消息？
- 如何使用异常信息引发错误？
回答by Michael Buen
To catch the error message and its code:
要捕获错误消息及其代码：
do $$
begin
create table yyy(a int);
create table yyy(a int); -- this will cause an error
exception when others then
raise notice 'The transaction is in an uncommittable state. '
'Transaction was rolled back';
raise notice '% %', SQLERRM, SQLSTATE;
end; $$
language 'plpgsql';
Haven't found the line number yet
还没有找到行号
UPDATE April, 16, 2019
更新 2019 年 4 月 16 日
As suggested by Diego Scaravaggi, for Postgres 9.2 and up, use GET STACKED DIAGNOSTICS:
正如 Diego Scaravaggi 所建议的，对于 Postgres 9.2 及更高版本，使用GET STACKED DIAGNOSTICS：
do language plpgsql $$
declare
v_state TEXT;
v_msg TEXT;
v_detail TEXT;
v_hint TEXT;
v_context TEXT;
begin
create table yyy(a int);
create table yyy(a int); -- this will cause an error
exception when others then
get stacked diagnostics
v_state = returned_sqlstate,
v_msg = message_text,
v_detail = pg_exception_detail,
v_hint = pg_exception_hint,
v_context = pg_exception_context;
raise notice E'Got exception:
state : %
message: %
detail : %
hint : %
context: %', v_state, v_msg, v_detail, v_hint, v_context;
raise notice E'Got exception:
SQLSTATE: %
SQLERRM: %', SQLSTATE, SQLERRM;
raise notice '%', message_text; -- invalid. message_text is contextual to GET STACKED DIAGNOSTICS only
end; $$;
Result:
结果：
NOTICE: Got exception:
state : 42P07
message: relation "yyy" already exists
detail :
hint :
context: SQL statement "create table yyy(a int)"
PL/pgSQL function inline_code_block line 11 at SQL statement
NOTICE: Got exception:
SQLSTATE: 42P07
SQLERRM: relation "yyy" already exists
ERROR: column "message_text" does not exist
LINE 1: SELECT message_text
^
QUERY: SELECT message_text
CONTEXT: PL/pgSQL function inline_code_block line 33 at RAISE
SQL state: 42703
Aside from GET STACKED DIAGNOSTICSis SQL standard-compliant, its diagnostics variables (e.g., message_text) are contextual to GSD only. So if you have a field named message_textin your table, there's no chance that GSD can interfere with your field's value.
除了GET STACKED DIAGNOSTICS符合 SQL 标准外，其诊断变量（例如，message_text）仅与 GSD 相关。因此，如果您message_text在表中命名了一个字段，则 GSD 不可能干扰您的字段的值。
Still no line number though.
仍然没有行号。
回答by Frank Heikens
Use the DO statement, a new option in version 9.0:
使用DO 语句，版本 9.0 中的一个新选项：
DO LANGUAGE plpgsql
$$
BEGIN
CREATE TABLE "Logs"."Events"
(
EventId BIGSERIAL NOT NULL PRIMARY KEY,
PrimaryKeyId bigint NOT NULL,
EventDateTime date NOT NULL DEFAULT(now()),
Action varchar(12) NOT NULL,
UserId integer NOT NULL REFERENCES "Office"."Users"(UserId),
PrincipalUserId varchar(50) NOT NULL DEFAULT(user)
);
CREATE TABLE "Logs"."EventDetails"
(
EventDetailId BIGSERIAL NOT NULL PRIMARY KEY,
EventId bigint NOT NULL REFERENCES "Logs"."Events"(EventId),
Resource varchar(64) NOT NULL,
OldVal varchar(4000) NOT NULL,
NewVal varchar(4000) NOT NULL
);
RAISE NOTICE 'Task completed sucessfully.';
END;
$$;
回答by Peter Eisentraut
You could write this as a psql script, e.g.,
你可以把它写成一个 psql 脚本，例如，
START TRANSACTION;
CREATE TABLE ...
CREATE TABLE ...
COMMIT;
\echo 'Task completed sucessfully.'
and run with
并运行
psql -f somefile.sql
Raising errors with parameters isn't possible in PostgreSQL directly. When porting such code, some people encode the necessary information in the error string and parse it out if necessary.
不能直接在 PostgreSQL 中使用参数引发错误。在移植这样的代码时，有些人会在错误字符串中编码必要的信息，并在必要时将其解析出来。
It all works a bit differently, so be prepared to relearn/rethink/rewrite a lot of things.
这一切都有些不同，所以准备重新学习/重新思考/重写很多东西。
回答by jhon Carrillo
Just want to add my two cents on this old post:
只是想在这个旧帖子上加上我的两分钱：
In my opinion, almost all of relational database engines include a commit transaction execution automatically after execute a DDL command even when you have autocommit=false, So you don't need to start a transaction to avoid a potential truncated object creation because It is completely unnecessary.
在我看来，几乎所有的关系数据库引擎都包含在执行 DDL 命令后自动执行提交事务，即使你有 autocommit=false，所以你不需要启动事务来避免潜在的截断对象创建，因为它是完全不必要。
