Re: enhanced error fields

2013/1/29 Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>:
> On 29 January 2013 17:05, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> Perhaps I'm mistaken, but I can't imagine that it would be terribly
>>> useful to anyone (including Pavel) to have a GET DIAGNOSTICS style
>>> ROUTINE_NAME.
>>
>> I hoped so I can use it inside exception handler
>
> Right, but is that really any use to you if it becomes available for a
> small subset of errors, specifically, errors that directly relate to
> the function? You're not going to be able to use it to trace the
> function where an arbitrary error occurred, if we do something
> consistent with GET DIAGNOSTICS as described by the SQL standard, it
> seems.

in this meaning is not too useful as I expected.

>
> I think that what the SQL standard intends here is actually consistent
> with what we're going to do with CONSTRAINT_NAME and so on. I just
> happen to think it's much less interesting, but am not opposed to it
> in principle (though I may oppose it in practice, if writing the
> feature means bloating up errdata).

I checked performance and Robert too, and there is not significant slowdown.

if I do

DROP FUNCTION inner(int);
DROP FUNCTION middle(int);
DROP FUNCTION outer();

CREATE OR REPLACE FUNCTION inner(int)
RETURNS int AS $$
BEGIN
RETURN 10/$1;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION middle(int)
RETURNS int AS $$
BEGIN
RETURN inner($1);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION outer()
RETURNS int AS $$
DECLARE
text_var1 text;
BEGIN
RETURN middle(0);
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = PG_EXCEPTION_CONTEXT;
RAISE NOTICE '>>%<<', text_var1;
RETURN -1;
END;
$$ LANGUAGE plpgsql;

SELECT outer();

then output is

psql:test.psql:34: NOTICE: >>PL/pgSQL function "inner"(integer) line
3 at RETURN
PL/pgSQL function middle(integer) line 3 at RETURN
PL/pgSQL function "outer"() line 5 at RETURN<<

I have not any possibility to take information about source of
exception without parsing context - and a string with context
information can be changed, so it is not immutable and not easy
accessible. Why I need this info - sometimes when I can log some info
about handled exception I don't would log a complete context due size
and due readability.

Another idea - some adjusted parser of context message can live in GET
STACKED DIAGNOSTICS implementation - so there can be some custom field
(just for GET STACKED DIAG.) that returns expected value. But this
value should be taken from context string with parsing - that is not
nice - but possible - I did similar game in orafce.

Regards

Pavel

>
> --
> Regards,
> Peter Geoghegan