Обсуждение: SQL state: 42601. Execption handling.

Поиск
Список
Период
Сортировка

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;
$$
 
--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;
$$
 
--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
 
 
 
 
 

Re: SQL state: 42601. Execption handling.

От
Tom Lane
Дата:
=?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

Re: SQL state: 42601. Execption handling.

От
Kyotaro Horiguchi
Дата:
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