Sample Header Ad - 728x90

How to log DML statements executed by a pl/pgsql function?

7 votes
3 answers
8702 views
I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log upon execution of the function with SELECT fnct_clear_temp_fields();. When I set log_statement = 'all', and execute the function I can see SELECT fnct_clear_temp_fields(); in the log, but not the underlying UPDATE commands. Is there a way to have the UPDATE commands appear in the log as well ? For info, here is the function: CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$ DECLARE --Put into a cursor a view dynamically listing all user-defined fields beginning with 'temp_' dataset_1 CURSOR FOR SELECT column_name, table_name FROM information_schema.tables NATURAL JOIN information_schema.columns WHERE table_schema='public' AND table_type='BASE TABLE' AND column_name ~ '^temp_' ORDER BY table_name,column_name; --Record variable to go through each line of the view above dataset_1_row RECORD; BEGIN OPEN dataset_1; --Open the cursor FETCH dataset_1 INTO dataset_1_row; --first row of the view WHILE FOUND LOOP RAISE NOTICE 'Table: %, Column: %', dataset_1_row.table_name,dataset_1_row.column_name; --Set to NULL the contents of the current 'temp_' column EXECUTE 'UPDATE '||dataset_1_row.table_name||' SET '||dataset_1_row.column_name||'=NULL WHERE '||dataset_1_row.column_name||' IS NOT NULL'; FETCH dataset_1 INTO dataset_1_row; --next row please. END LOOP; --while end CLOSE dataset_1; RETURN; END; $$ LANGUAGE plpgsql;
Asked by Sébastien Clément (1825 rep)
Jul 10, 2014, 03:55 PM
Last activity: Jul 17, 2023, 10:27 PM