Re: SQL state: 42601. Execption handling.

At Tue, 07 Jun 2022 10:36:52 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in
> =?UTF-8?B?0KY=?= <pfunk(at)mail(dot)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