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 $$
    stmt RECORD;
    statements cursor for select tablename from pg_tables where tableowner = username;
    for stmt in statements loop
        execute 'truncate table ' || quote_ident(stmt.tablename) || ' CASCADE ;';
    end loop;
$$ language 'plpgsql';

I get the following error:

ERROR: syntax at or near "$1"    LINE1:   $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.

  • It's a third party database. Just to explain the context: I want to be able to backup production databases onto our developement databases for debugging and diagnose... and it seems I have to truncate all our local tables before restoring, otherwise pg_restore thows hundreds of errors because of dupplicate keys. Aug 22 '12 at 14:54
  • 2
    "...it seems I have to truncate all our local tables before restoring..." The pg_dump utility can drop database objects before trying to create them. See the --clean argument on this page. Depending on your environment, it might be practical to drop the dev database before restoring to it. (Make sure the dump creates the database before you do that.) Aug 22 '12 at 16:04
  • @Catcall, I'm dumping production databases and I don't want them to be cleared. What's updated I want to restore in developement databases, on which we can diagnose and debug. Aug 22 '12 at 16:14
  • 1
    @Stephane I don't think Catcall is referring to clearing the source database - rather generating sql to drop and recreate the tables in the development database. Aug 22 '12 at 16:49
  • 1
    Sadly, the answers for the question at SO where you got your advice from were not very good (IMO). I added an alternative answer there. Aug 22 '12 at 22:04

Try dispensing with the explicit cursor:

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;
    for r in (select tablename from pg_tables where tableowner = username) loop
        execute 'truncate table ' || quote_ident(r.tablename) || ' cascade';
    end loop;
$$ language 'plpgsql';
select truncate_tables('stack');
select * from foo;
(0 rows)
  • also check you can run select tablename from pg_tables where tableowner = 'foo' as the user running the script Aug 22 '12 at 16:18
  • Bingo ! Does that mean that PL/PgSql doesn't support cursors ? Aug 22 '12 at 16:21
  • I'm trying to find some doc on cursor limitation in 8.3. Maybe that's a known issue. Aug 22 '12 at 16:26
  • All in all... for the moment I have found no limitation regarding cursors in 8.3 in documentation. Once again many thx Jack! Aug 22 '12 at 16:49
  • 2
    Detail you might be interested in: Unlike expression IN (subquery) in SQL, FOR target IN query LOOP in plpgsql does not require parenthesis around the query. Aug 23 '12 at 1:09

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)
             || string_agg(quote_ident(t.tablename), ', ')
             || ' CASCADE;'
      FROM   pg_tables t
      WHERE  t.tableowner = _username
      AND    t.schemaname = 'public'
$func$ LANGUAGE plpgsql;


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.

  • +1 excellent. As you say elsewhere, simpler and faster. Aug 23 '12 at 8:12

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.