Início / dba / Perguntas / 22909
Accepted
Stephane Rolland
Asked: 2012-08-23 06:28:12 +0800 CST

Bug na criação da função PL/pgSQL

  • 772

Não sei se essa pergunta se encaixa melhor aqui ou em SO...

Este é um script que gostaria de iniciar (o código da função foi copiado de uma pergunta no SO ):

\c mydb

create or replace function truncate_tables(username in varchar) returns void as $$
declare
    stmt RECORD;
    statements cursor for select tablename from pg_tables where tableowner = username;
begin 
    for stmt in statements loop
        execute 'truncate table ' || quote_ident(stmt.tablename) || ' CASCADE ;';
    end loop;
end;
$$ language 'plpgsql';

Estou tendo o erro a seguir:

ERROR: syntax at or near "$1"    LINE1:   $1
QUERY $1
CONTEXT: SQL statement in PL/PgSQL function "truncate_tables" near line 5

Sou novo em Postgres e PL/pgSQL e não sei o que essa mensagem de erro significa.

postgresql cursors

2 respostas

  1. Este exemplo particular pode ser mais simples.
    Você pode TRUNCATEvárias tabelas ao mesmo tempo. Agregue todos os nomes de tabelas e execute uma única instrução:

    CREATE OR REPLACE FUNCTION truncate_tables(_username text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
      SELECT 'TRUNCATE TABLE '
             || string_agg(quote_ident(t.tablename), ', ')
             || ' CASCADE;'
      FROM   pg_tables t
      WHERE  t.tableowner = _username
      AND    t.schemaname = 'public'
   );
END;
$func$ LANGUAGE plpgsql;

    Ligar:

    SELECT truncate_tables('postgres');

    string_agg()requer PostgreSQL 9.0 ou posterior.
    Em 8.4 você pode substituir:

    array-to_string(array_agg(quote_ident(t.tablename)), ', ')

    Para a versão 8.3, você escreveria sua própria função de agregação - bastante simples, mas não mais simples do que a solução de loop.

    O desempenho diminui ao excluir ou truncar muitas tabelas de uma vez no PostgreSQL 9.1. Uma correção para isso na próxima versão 9.2. Cito as notas de lançamento :

    Melhorar o desempenho da fila de solicitação fsync do checkpointer quando muitas tabelas estão sendo descartadas ou truncadas (Tom Lane)

    Tópico relacionado em pgsql-hackers.

    A resposta relacionada de @Craig no SO me ajudou a descobrir isso.

    • 5
  2. Best Answer

    Tente dispensar o cursor explícito:

    begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create table foo(id serial);
insert into foo default values;
create or replace function truncate_tables(username in varchar) returns void as $$
declare r record;
begin 
    for r in (select tablename from pg_tables where tableowner = username) loop
        execute 'truncate table ' || quote_ident(r.tablename) || ' cascade';
    end loop;
end;
$$ language 'plpgsql';
--
select truncate_tables('stack');
select * from foo;
/*
 id
----
(0 rows)
*/
--
rollback;
    • 4

relate perguntas