DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=WWWjl4dmCoReYgBxQDkK/TDspzXCoBrCt3DxuYYmG8JjftCayBqPLAM5NfuqIDgGmg
	ye+VlGBi/0AfxxCt2KjUM9n/lQF3v2BPcSyFDF56JPsi3hNfEuh99yieMxt4MD5sjrBy
	6WY92NUrCfAm5qppCQJq3b+L4WnCZfGLYfR7A=
Message-ID: <4DE1FDD8.2080309@gmail.com>
Date: Sun, 29 May 2011 10:03:36 +0200
From: Surfing <onlinesurfing@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; it;
	rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Function to total reset a schema
References: <4DE1F7DC.3080700@gmail.com> <irsu3s$4b9$1@dough.gmane.org>
In-Reply-To: <irsu3s$4b9$1@dough.gmane.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

I have two schemas, so I can't do that ...
@ Scott. You are totally right ... I have just read this on the 
documentation:

"TRUNCATE quickly removes all rows from a set of tables. It has the same 
effect as an unqualiﬁed
DELETE on each table, but since it does not actually scan the tables it 
is faster. Furthermore, it reclaims
disk space immediately, rather than requiring a subsequent VACUUM 
operation. This is most useful on
large tables."

So the VACUUM should not really be necessary..thanks.

Il 29/05/2011 09:54, Thomas Kellerer ha scritto:
> Surfing wrote on 29.05.2011 09:38:
>> Hi all,
>> I need to write a function that totally empty a schema.
>>
>> So I have written a TRUNCATE statement for each table and set to 0 
>> each sequence.
>> Btw, it could be good to execute a vacuum statement on each table, 
>> but from within the function this is not allowed.
>>
>> Is there a way to obtain the same result in another way (without 
>> using the vacuum)?
>>
>
> If you are only using a single schema in your database, you could 
> create an empty database with all your tables that you then use as the 
> template database when creating a new one:
>
> Then each time you want to reset your schema (=database) you do a
>
> drop database real_database;
> create database real_database template template_database;
>
> Then you don't need to adjust your "reset" script if your database 
> changes (you only maintain the template database using your SQL scripts)
>
> Thomas
>
>