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
Attachments:
"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 existline 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 existline 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