pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Fuhr <mike@fuhr.org>
To: Abraham, Danny <danny_abraham@bmc.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Is there a way to run CREATE TABLESPACE within a stored procedure?
Date: Fri, 20 Jul 2007 08:14:15 -0600
Message-ID: <20070720141415.GB85514@winnie.fuhr.org> (raw)
In-Reply-To: <F8B9B12641FB6B4FAC761BDEF9D63B040DD93B29@tlv-ex-01.adprod.bmc.com>
References: <F8B9B12641FB6B4FAC761BDEF9D63B040DD93B29@tlv-ex-01.adprod.bmc.com>

On Thu, Jul 19, 2007 at 02:53:50PM +0300, Abraham, Danny wrote:
> Fails on: Cannot run inside a transaction block.

PostgreSQL functions run in the context of a transaction so they
can't execute statements that aren't allowed inside a transaction
block.  A workaround is to have the function connect to the database
via contrib/dblink (or dbi-link, or some such mechanism) and execute
the statements over that connection.  Caveat: if the function's
transaction rolls back then statements already committed over the
connection won't be rolled back.

-- 
Michael Fuhr



view thread (2+ messages)

Message-ID: <20070720141415.GB85514@winnie.fuhr.org>
Permalink:  ../20070720141415.GB85514@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20070720141415.GB85514@winnie.fuhr.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: mike@fuhr.org, danny_abraham@bmc.com
  Subject: Re: Is there a way to run CREATE TABLESPACE within a stored procedure?
  In-Reply-To: <20070720141415.GB85514@winnie.fuhr.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox