5

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;
  • If you need it only in this function, you can RAISE LOG '%', your_statement; Also, I find the FOR dataset_1_row IN SELECT ... LOOP construct much smoother. – dezso Jul 10 '14 at 16:18
  • Thanks for the tips dezso, well appreciated. The FOR ... LOOP is indeed a much better construction. About the RAISE LOG '%', I have put it directly in front of my EXECUTE 'UPDATE'... command, but it gives me: ERROR: type "execute" does not exist. – Sébastien Clément Jul 10 '14 at 18:06
  • PostgreSQL doesn't have built-in support for logging all DML run within functions. It'd be useful to have for a narrow subset of cases, but nobody's implemented it yet. The new pgaudit extension (in development for 9.5, may be usable with 9.4 too) should add this facility. – Craig Ringer Jul 11 '14 at 2:40
2

So, my suggestion as an actual answer:

If you need it only in this function, you can do a RAISE LOG '%', your_statement;, or in your actual code:

...
DECLARE
    exec_str text;
...
    --Set to NULL the contents of the current 'temp_' column
    exec_str := 'UPDATE '||dataset_1_row.table_name||
                'SET '||dataset_1_row.column_name||'=NULL 
                 WHERE '||dataset_1_row.column_name||' IS NOT NULL';
    RAISE LOG 'Query executed: %', exec_str;
    EXECUTE exec_str;
...

Also, I find the

FOR dataset_1_row IN SELECT ... 
LOOP 
END LOOP;

construct much smoother.

7

Question asked

There is a built-in way to log all statements inside plpgsql functions: auto-explain

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries
SET auto_explain.log_nested_statements = ON; -- statements inside functions

Details under this closely related question:
Postgres query plan of a UDF invocation written in pgpsql

Potentially generates a lot of log output. I would only use it for debugging, not in production.
If you just need the one statement logged, go with @dezso's advice.

Code audit

Consider this rewritten function:

CREATE OR REPLACE FUNCTION fnct_clear_temp_fields()
  RETURNS void AS
$func$
DECLARE
   rec record;
   qry text;
BEGIN
   FOR rec IN
      SELECT quote_ident(c.relname) AS tbl, quote_ident(a.attname) AS col
      FROM   pg_namespace n
      JOIN   pg_class     c ON c.relnamespace = n.oid
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  n.nspname = 'public'
      AND    c.relkind = 'r'
      AND    a.attname LIKE 'temp_%'  -- LIKE is faster than ~
      AND    a.attnum > 0
      AND    NOT a.attisdropped
      ORDER  BY 1,2
   LOOP
      RAISE NOTICE 'Table: %, Column: %', rec.tbl, rec.col;
      qry := format('UPDATE %1$s SET %2$s = NULL WHERE %2$s IS NOT NULL', rec.tbl, rec.col);
      RAISE LOG 'Query: %', qry;
      EXECUTE qry;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Major points

  • You must sanitize all identifiers that you build into dynamic SQL, else it can fail with non-standard names that require double-quoting. Worse, you are open to SQL injection.
    Demonstrating quote_ident(), since you are using the sanitized identifiers multiple times. There are more options with regclass or format():
    Table name as a PostgreSQL function parameter

  • I prefer to base such queries on the system catalog instead of the painfully slow views of the information schema. That's a matter of requirements and taste, though. Demonstrating the equivalent, which is ~ 10 times faster (not regarding UPDATE commands). More:
    How to check if a table exists in a given schema

  • LIKE is generally faster than the more powerful regular expression matching (~). If LIKE can do the job, use it.

  • Some other minor simplifications.

  • Related answer with more details:
    Update record of a cursor where the table name is a parameter

1

Great dezso, it works! Here is the final version of my function:

CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$
DECLARE
    dataset_1_row RECORD; --Record variable to go through each row of the view below
    update_query TEXT; --The dynamic UPDATE query to be executed

BEGIN
    FOR dataset_1_row IN --Cycle through rows of query below
            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
        LOOP

        RAISE NOTICE 'Table: %, Column: %',  dataset_1_row.table_name,dataset_1_row.column_name;
        --Create a dynamic update query to set to NULL the contents of the current 'temp_' column
        update_query :='UPDATE '||dataset_1_row.table_name||' SET '||dataset_1_row.column_name||'=NULL WHERE '||dataset_1_row.column_name||' IS NOT NULL;';
        RAISE LOG 'Query executed: %', update_query; --Put query def in log
        EXECUTE update_query; --Run the query
    END LOOP; --Next line of SELECT query above 

    RETURN;
END;
$$ LANGUAGE plpgsql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.