From: Greg Wittel <gwittel@proofpoint.com>
To: pgsql-sql@postgresql.org
Subject: Odd PL/PgSQL Error -- relation "X" does not exist when using index expression
Date: Wed, 07 Feb 2007 14:43:13 -0800
Message-ID: <45CA5601.1090007@proofpoint.com> (raw)
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
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-sql@postgresql.org
Cc: gwittel@proofpoint.com
Subject: Re: Odd PL/PgSQL Error -- relation "X" does not exist when using index expression
In-Reply-To: <45CA5601.1090007@proofpoint.com>
* 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