Thread: Use of ?get diagnostics'?
Hi all I try to create a function (code at https://pastebin.com/mTs18B90) using 'get diagnostics' to retrieve the number of affected rows. However, it throws the following exception was thrown: SQLSTATE: 42703 column "row_count" does not exist when I test it with drop table if exists TEST_EXECUTE_WO_RETURN_LOGGED; create table TEST_EXECUTE_WO_RETURN_LOGGED(I bigint, C char(2)); commit; select EXECUTE_WO_RETURN_LOGGED( I_STATEMENT_TO_EXECUTE => $$insert into TEST_EXECUTE_WO_RETURN_LOGGED(I, C) values (1, 'ab');$$, I_LEVEL => 'LOG', I_REPORT_ERRORS_ONLY => true ); If you want to try out the code, be aware that it uses pglogger and pgutils (both on SourceForge maybe not there in the version yet needed, work is ongoing) such that you might want to strip the respective calls. I created another function using 'get diagnostics' that works - it is part of pglogger. Code snipped $body$ declare C_LOGGING_LEVEL_PROPERTY_NAME constant text := 'LOGGING_LEVEL'; V_ROW_COUNT bigint; begin update PROPERTY set PROPERTY_VALUE_STRING = I_LEVEL where PROPERTY_NAME = C_LOGGING_LEVEL_PROPERTY_NAME; get current diagnostics V_ROW_COUNT = ROW_COUNT; I did not find the error I am making. Kind regards Thiemo -- S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH Signal (Safer than WhatsApp): +49 1578 7723737 Handys: +41 78 947 36 21 | +49 1578 772 37 37 Tox-Id: B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B
Attachment
>>>>> "Thiemo" == Thiemo Kellner <thiemo@gelassene-pferde.biz> writes: Thiemo> Hi all Thiemo> I try to create a function (code at Thiemo> https://pastebin.com/mTs18B90) Paste sites are for IRC, on the mailing list you should always attach the necessary details to your message. Thiemo> using 'get diagnostics' to retrieve the number of affected Thiemo> rows. However, it throws Thiemo> the following exception was thrown: Thiemo> SQLSTATE: 42703 Thiemo> column "row_count" does not exist line 44 of your paste: V_TEXT := V_TEXT || ROW_COUNT || ' row.'; should be V_ROW_COUNT, I suspect. Likewise line 46. (The CONTEXT lines of the error message would have identified the offending line of the function for you.) -- Andrew (irc:RhodiumToad)
Hi Andrew > Paste sites are for IRC, on the mailing list you should always attach > the necessary details to your message. Ok, I was under the impression that paste site were preferable to attachments which generates traffic not everyone is interested in. > Thiemo> the following exception was thrown: > Thiemo> SQLSTATE: 42703 > Thiemo> column "row_count" does not exist > > line 44 of your paste: V_TEXT := V_TEXT || ROW_COUNT || ' row.'; > > should be V_ROW_COUNT, I suspect. Likewise line 46. You are perfectly right and now I feel a bit stupid. Many thanks! Maybe others had the same idea, but it would help me, if the exception contained a line where the error was found. Though, I am not quite sure whether this is just due to my error handling in the function. Kind regards Thiemo -- S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH Signal (Safer than WhatsApp): +49 1578 7723737 Handys: +41 78 947 36 21 | +49 1578 772 37 37 Tox-Id: B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B
On 9/22/19 11:33 AM, Thiemo Kellner wrote:
> Hi Andrew
>
>> Paste sites are for IRC, on the mailing list you should always attach
>> the necessary details to your message.
>
> Ok, I was under the impression that paste site were preferable to
> attachments which generates traffic not everyone is interested in.
>
>> Thiemo> the following exception was thrown:
>> Thiemo> SQLSTATE: 42703
>> Thiemo> column "row_count" does not exist
>>
>> line 44 of your paste: V_TEXT := V_TEXT || ROW_COUNT || ' row.';
>>
>> should be V_ROW_COUNT, I suspect. Likewise line 46.
>
> You are perfectly right and now I feel a bit stupid. Many thanks!
>
> Maybe others had the same idea, but it would help me, if the exception
> contained a line where the error was found. Though, I am not quite sure
> whether this is just due to my error handling in the function.
It should:
create table diag_test(id integer);
insert into diag_test values (1), (2);
CREATE OR REPLACE FUNCTION public.get_diag_test()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
V_ROW_COUNT BIGINT DEFAULT 0;
V_TEXT text;
BEGIN
PERFORM * FROM diag_test;
get current diagnostics V_ROW_COUNT = ROW_COUNT;
V_TEXT := ROW_COUNT || ' row.';
END;
$function$
test=# select get_diag_test();
ERROR: column "row_count" does not exist
LINE 1: SELECT ROW_COUNT || ' row.'
^
QUERY: SELECT ROW_COUNT || ' row.'
CONTEXT: PL/pgSQL function get_diag_test() line 9 at assignment
To get above I believe you will need to use GET CURRENT DIAGNOSTICS
PG_CONTEXT:
https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
and example:
https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-CALL-STACK
>
> Kind regards
>
> Thiemo
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hello Adrian Quoting Adrian Klaver <adrian.klaver@aklaver.com>: > To get above I believe you will need to use GET CURRENT DIAGNOSTICS > PG_CONTEXT: I actually use "get stacked diagnostics" to retrieve the exception place. And it works. I am not sure why I did no see it. However, I noticed, that the stack does not include the error place in dynamic SQL executed by the "execute" command. Maybe I am missing something again. Kind regards Thiemo -- S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH Signal (Safer than WhatsApp): +49 1578 7723737 Handys: +41 78 947 36 21 | +49 1578 772 37 37 Tox-Id: B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B
On 9/25/19 10:44 PM, Thiemo Kellner wrote: > Hello Adrian > > Quoting Adrian Klaver <adrian.klaver@aklaver.com>: > >> To get above I believe you will need to use GET CURRENT DIAGNOSTICS >> PG_CONTEXT: > > I actually use "get stacked diagnostics" to retrieve the exception > place. And it works. I am not sure why I did no see it. GET [ CURRENT ] DIAGNOSTICS: https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS and GET STACKED DIAGNOSTICS https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS are two different things. See the example here: https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-CALL-STACK > > However, I noticed, that the stack does not include the error place in > dynamic SQL executed by the "execute" command. Maybe I am missing > something again. > > Kind regards > > Thiemo > -- Adrian Klaver adrian.klaver@aklaver.com