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.klaver@aklaver.com