Re: SQL state: 42601. Execption handling.
At Tue, 07 Jun 2022 10:36:52 -0400, Tom Lane wrote in > =?UTF-8?B?0KY=?= 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
Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?
On Sat, Jun 4, 2022 at 12:37 AM Tom Lane wrote:
> Bryn Llewellyn writes:
> > I'm going to try to think like this:
> > The number of possible spellings of the names of keys in a JSON object
> is some flavor of infinite. So including this in an object:
> > "k": null
> > really is saying something. It says that I do know about "k" and that
> yet I have simply no information available about its value.
>
> I'd read it as asserting that key "k" is meaningful for this object,
> but the correct value for that key is not known.
>
> I have a hard time with your assertion that {"x": 42, "y": null}
> should be considered equivalent to {"x": 42}, because it would
> render key-exists predicates useless. Either you have to say that
> key "y" is claimed to exist in both of these objects and indeed every
> object, or you have to make it fail if the key's value is null (so that
> it'd say "false" in both of these cases). Either of those options
> seems both weird and useless.
>
yeah. I would expect for json or jsonb, two values, a, b,
a is distinct from b
should give the same answer as
a::text is distinct from b::text
merlin
Re: SQL state: 42601. Execption handling.
=?UTF-8?B?0KY=?= 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
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 --
SQL state: 42601. Execption handling.
Community, is that behavior is explainable ? 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 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?
