Re: Use of ?get diagnostics'?

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(dot)klaver(at)aklaver(dot)com