Bug in PL/pgSQL function creation

Question

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.

• Stephane Rolland: 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.
• Mike Sherrill 'Cat Recall': "...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.)
• Stephane Rolland: @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.
• Jack Douglas: @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.
• Erwin Brandstetter: 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.

Answer 1

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;

• Jack Douglas: also check you can run select tablename from pg_tables where tableowner = 'foo' as the user running the script
• Stephane Rolland: Bingo ! Does that mean that PL/PgSql doesn't support cursors ?
• Stephane Rolland: I'm trying to find some doc on cursor limitation in 8.3. Maybe that's a known issue.
• Stephane Rolland: All in all... for the moment I have found no limitation regarding cursors in 8.3 in documentation. Once again many thx Jack!
• Erwin Brandstetter: 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.

Answer 2

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.

• Jack Douglas: +1 excellent. As you say elsewhere, simpler and faster.