Início / dba / Perguntas / 71178
Accepted
Sébastien Clément
Asked: 2014-07-11 07:55:52 +0800 CST

Como registrar instruções DML executadas por uma função pl/pgsql?

  • 772

Eu tenho uma função pl/pgsql (veja abaixo) que lista alguns campos e limpa seu conteúdo usando comandos UPDATE construídos dinamicamente.

Quando defino log_statement = 'mod', não consigo ver nada no log durante a execução da função com SELECT fnct_clear_temp_fields();. Quando defino log_statement = 'all'e executo a função, posso ver SELECT fnct_clear_temp_fields();no log, mas não os comandos UPDATE subjacentes.

Existe uma maneira de fazer com que os comandos UPDATE apareçam no log também?

Para informações, aqui está a função:

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;
postgresql functions

3 respostas

  1. Pergunta feita

    Existe uma maneira interna de registrar todas as instruções dentro das funções plpgsql: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

    Detalhes nesta questão intimamente relacionada:
    Plano de consulta Postgres de uma invocação UDF escrita em pgpsql

    Potencialmente gera muita saída de log. Eu só usaria para depuração, não em produção.
    Se você só precisa de uma declaração registrada, siga o conselho de @dezso .

    Auditoria de código

    Considere esta função reescrita:

    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;

    Pontos principais

    • 8
  2. Best Answer

    Então, minha sugestão como uma resposta real:

    Se você precisar apenas nesta função, você pode fazer um RAISE LOG '%', your_statement;, ou no seu código real:

    ...
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;
...

    Além disso, acho o

    FOR dataset_1_row IN SELECT ... 
LOOP 
END LOOP;

    construir muito mais suave.

    • 3

  3. Ótimo dezso, funciona! Aqui está a versão final da minha função:

    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;
    • 1

relate perguntas