On 11/24/2012 02:15 AM, Peter Kroon wrote:
> I found out that declaring tables outside of functions increases the
> execution time of the function.
Strictly, what's probably happening is that creating a table in the same
transaction as populating it is a lot faster than creating it,
committing, and populating it in a new transaction. In the 1st case WAL
logging for the heap can be avoided if you aren't using replication or
PITR (ie wal_level is minimal).
Functions are automatically wrapped in a transaction if you don't open
one explicitly so doing a CREATE TABLE inside a function will be
quicker. The same result should be achieved by beginning a transaction,
creating the table, then calling the function.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services