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
Last activity: Jul 17, 2023, 10:27 PM