Date: Thu, 8 Feb 2007 11:14:33 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: Greg Wittel <gwittel@proofpoint.com>, pgsql-sql@postgresql.org
Subject: Re: Odd PL/PgSQL Error -- relation "X" does not exist when using
	index expression
Message-ID: <20070208141433.GD24069@alvh.no-ip.org>
Mail-Followup-To: Michael Fuhr <mike@fuhr.org>,
	Greg Wittel <gwittel@proofpoint.com>, pgsql-sql@postgresql.org
References: <45CA5601.1090007@proofpoint.com>
	<20070208112331.GA75899@winnie.fuhr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20070208112331.GA75899@winnie.fuhr.org>
User-Agent: Mutt/1.5.13 (2006-08-11)

Michael Fuhr wrote:
> On Wed, Feb 07, 2007 at 02:43:13PM -0800, Greg Wittel wrote:
> > I'm having a strange problem with a PL/PGSQL query that executes some 
> > dynamic SQL code.  The code basically creates a dynamically named table, 
> > some indexes, etc.
> > 
> > The problem seems to be the an index expression. If I remove it and do a 
> > plain index on the column, all works correctly.  If I keep it, I get a 
> > "relation does not exist" error.
> 
> The error appears to happen for anything that uses SPI.  A C function
> that executes the following fails with the same error:
> 
> SPI_exec("CREATE TABLE foo (t text); CREATE INDEX foo_idx ON foo (lower(t))", 0);

Hmm, are we short of a CommandCounterIncrement in the middle of both
commands?  Does the same error show up if you do

SPI_exec("CREATE TABLE foo (t text);", 0);
SPI_exec("CREATE INDEX foo_idx ON foo (lower(t))", 0);
?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support