Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Stephane Rolland 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.) –  Mike Sherrill 'Cat Recall' 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. –  Stephane Rolland 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. –  Jack Douglas 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. –  Erwin Brandstetter Aug 22 '12 at 22:04
show 1 more comment

2 Answers

up vote 4 down vote accepted

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;
share|improve this answer
    
also check you can run select tablename from pg_tables where tableowner = 'foo' as the user running the script –  Jack Douglas Aug 22 '12 at 16:18
    
Bingo ! Does that mean that PL/PgSql doesn't support cursors ? –  Stephane Rolland 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. –  Stephane Rolland 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! –  Stephane Rolland 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. –  Erwin Brandstetter Aug 23 '12 at 1:09
show 2 more comments

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.

share|improve this answer
    
+1 excellent. As you say elsewhere, simpler and faster. –  Jack Douglas Aug 23 '12 at 8:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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