Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 812BE9FB2EA for ; Thu, 8 Feb 2007 07:23:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 83890-02 for ; Thu, 8 Feb 2007 07:23:35 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id 1B2069FA30D for ; Thu, 8 Feb 2007 07:23:38 -0400 (AST) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id l18BNYLw007397 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 8 Feb 2007 04:23:36 -0700 (MST) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.8/8.13.8) with ESMTP id l18BNX2c076408; Thu, 8 Feb 2007 04:23:33 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.8/8.13.8/Submit) id l18BNVkC076407; Thu, 8 Feb 2007 04:23:31 -0700 (MST) (envelope-from mfuhr) Date: Thu, 8 Feb 2007 04:23:31 -0700 From: Michael Fuhr To: Greg Wittel Cc: pgsql-sql@postgresql.org Subject: Re: Odd PL/PgSQL Error -- relation "X" does not exist when using index expression Message-ID: <20070208112331.GA75899@winnie.fuhr.org> References: <45CA5601.1090007@proofpoint.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <45CA5601.1090007@proofpoint.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/82 X-Sequence-Number: 27416 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); In 8.2.3 the error location is: LOCATION: RangeVarGetRelid, namespace.c:200 -- Michael Fuhr