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
