Bug in PL/pgSQL function creation

WePython 1 Months+

I don't know if this question better suits here or in SO ...

This is a script that I'd like to launch (the code of the function was copied from a question on 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';

I get the following error:

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

I am new to Postgres and PL/pgSQL and don't know what this error message means.

-----------------Answer-----------------

Try dispensing with the explicit cursor:

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

This particular example can be simpler.
You can TRUNCATE multiple tables at once. Aggregate all tablenames and execute a single statement:

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;

Call:

SELECT truncate_tables('postgres');

string_agg() requires PostgreSQL 9.0 or later.
In 8.4 you can substitute:

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

For v8.3 you would write your own aggregate function - rather simple, yet not simpler than the looping solution any more.


Performance degrades when deleting or truncating many tables at once in PostgreSQL 9.1. A fix for this in the upcoming version 9.2. I quote the release notes:

Improve performance of checkpointer's fsync-request queue when many tables are being dropped or truncated (Tom Lane)

Related thread at pgsql-hackers.

@Craig's related answer at SO helped me discover this.


Previous : How can I save a severely fragmented database/table?
Next : Oracle: deleting data from tables leads to data file fragmentation?