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@xxxxxxxxxxx
