Message-ID: <45CA5601.1090007@proofpoint.com>
Date: Wed, 07 Feb 2007 14:43:13 -0800
From: Greg Wittel <gwittel@proofpoint.com>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Odd PL/PgSQL Error -- relation "X" does not exist when using index
	expression
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I've tried this on 8.2.1, .2 and .3:

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.

If I were to take the generated code and run it manually, it works fine. It 
only fails when run inside the stored procedure.

---------------------------------------
-- This one works
CREATE OR REPLACE FUNCTION init_testdata_a(sourceid_ integer) RETURNS void AS
$DELIM$
DECLARE
    sqlquery_ varchar;
BEGIN
    sqlquery_ := '
DROP TABLE IF EXISTS testdata_' || sourceid_ || ' CASCADE;
CREATE TABLE testdata_' || sourceid_ || ' (
    id SERIAL PRIMARY KEY,
    data text NOT NULL
);
CREATE INDEX testdata_' || sourceid_ || '_content_idx on testdata_' || 
sourceid_ || ' (data);
';
    --RAISE NOTICE '%', sqlquery_;
    EXECUTE sqlquery_;
END;
$DELIM$ LANGUAGE PLPGSQL;

-- Adding the lower() causes it to not work
CREATE OR REPLACE FUNCTION init_testdata_b(sourceid_ integer) RETURNS void AS
$DELIM$
DECLARE
    sqlquery_ varchar;
BEGIN
    sqlquery_ := '
DROP TABLE IF EXISTS testdata_' || sourceid_ || ' CASCADE;
CREATE TABLE testdata_' || sourceid_ || ' (
    id SERIAL PRIMARY KEY,
    data text NOT NULL
);
CREATE INDEX testdata_' || sourceid_ || '_content_idx on testdata_' || 
sourceid_ || ' ( lower(data) );
';
    --RAISE NOTICE '%', sqlquery_;
    EXECUTE sqlquery_;
END;
$DELIM$ LANGUAGE PLPGSQL;
---------------------------------------


For example, running:

=> select init_testdata_a(1);
....works....

=> select init_testdata_b(2);
....
"
PL/pgSQL function "init_testdata_b" line 13 at execute statement
ERROR:  relation "testdata_2" does not exist
CONTEXT:  SQL statement "
...


Any thoughts?

-Greg