Thread: SQL state: 42601. Execption handling.
Community, is that behavior is explainable?
I suppose the reason of such behavior in different steps of code parsing, but can`t find any discussions or topics in documentation.
Сould someone clarify the situation for sure?
create or replace function dbo.func(result out int) as $$begin result = 1; end; $$ language plpgsql;
-- 1 --
do $$
declare
v_result int;
begin
select p.result from dbo.func(d) p into v_result;
exception when SQLSTATE '42601' then
raise '42601';
when others then
raise 'others';
end;
$$
-- 1 --
do $$
declare
v_result int;
begin
select p.result from dbo.func(d) p into v_result;
exception when SQLSTATE '42601' then
raise '42601';
when others then
raise 'others';
end;
$$
--Exception is handled. This is OK.
ERROR: others
CONTEXT: PL/pgSQL function inline_code_block line 9 at RAISE
********** Error **********
ERROR: others
SQL state: P0001
Context: PL/pgSQL function inline_code_block line 9 at RAISE
-- 2 --
do $$
begin
select p.result from dbo.func() p into v_result;
exception when SQLSTATE '42601' then
raise '42601';
when others then
raise 'others';
end;
$$
ERROR: others
CONTEXT: PL/pgSQL function inline_code_block line 9 at RAISE
********** Error **********
ERROR: others
SQL state: P0001
Context: PL/pgSQL function inline_code_block line 9 at RAISE
-- 2 --
do $$
begin
select p.result from dbo.func() p into v_result;
exception when SQLSTATE '42601' then
raise '42601';
when others then
raise 'others';
end;
$$
--Exception is not handled. This is the question.
ERROR: "v_result" is not a known variable
LINE 3: select p.result from dbo.func() p into v_result;
********** Error **********
ERROR: "v_result" is not a known variable
SQL state: 42601
Character: 57
ERROR: "v_result" is not a known variable
LINE 3: select p.result from dbo.func() p into v_result;
********** Error **********
ERROR: "v_result" is not a known variable
SQL state: 42601
Character: 57
=?UTF-8?B?0KY=?= <pfunk@mail.ru> writes:
> -- 2 --
> do $$
> begin
> select p.result from dbo.func() p into v_result;
> exception when SQLSTATE '42601' then
> raise '42601' ;
> when others then
> raise 'others' ;
> end ;
> $$
>
> --Exception is not handled. This is the question.
> ERROR: "v_result" is not a known variable
> LINE 3 : select p.result from dbo.func() p into v_result;
This error is thrown by the plpgsql parser, so you can't trap it
with "exception", any more than you could trap other syntax errors
detected by the parser. (As an extreme example, you could certainly
not trap it if you misspelled "exception".)
regards, tom lane
At Tue, 07 Jun 2022 10:36:52 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote in > =?UTF-8?B?0KY=?= <pfunk@mail.ru> writes: > > -- 2 -- > > do $$ > > begin > > select p.result from dbo.func() p into v_result; > > exception when SQLSTATE '42601' then > > raise '42601' ; > > when others then > > raise 'others' ; > > end ; > > $$ > > > > --Exception is not handled. This is the question. > > ERROR: "v_result" is not a known variable > > LINE 3 : select p.result from dbo.func() p into v_result; > > This error is thrown by the plpgsql parser, so you can't trap it > with "exception", any more than you could trap other syntax errors > detected by the parser. (As an extreme example, you could certainly > not trap it if you misspelled "exception".) FWIW, you can see the difference as the following difference. =# CREATE OR REPLACE FUNCTION f1() RETURNS void AS $$ declare v_result int; begin select p.result from dbo.func(d) p into v_result; end ; $$ LANGUAGE plpgsql; CREATE FUNCTION (Succeeds. That is, plpgsql parser doesn't catch it.) =# select f1(); ERROR: column "d" does not exist (Caught by SQL parser executed at runtime) =# CREATE OR REPLACE FUNCTION f2() RETURNS void AS $$ begin select p.result from dbo.func() p into v_result; end ; $$ LANGUAGE plpgsql; ERROR: "v_result" is not a known variable LINE 3: select p.result from dbo.func() p into v_result; (Fails, as plpgsql parser caught it.) regards. -- Kyotaro Horiguchi NTT Open Source Software Center