| From: | bryan(at)unhwildhats(dot)com | 
|---|---|
| To: | pgsql-bugs(at)postgresql(dot)org | 
| Subject: | BUG #11526: WITH tables not accessible from function | 
| Date: | 2014-09-30 16:05:52 | 
| Message-ID: | 20140930160552.17172.90970@wrigleys.postgresql.org | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-bugs | 
The following bug has been logged on the website:
Bug reference:      11526
Logged by:          Bryan
Email address:      bryan(at)unhwildhats(dot)com
PostgreSQL version: 9.3.5
Operating system:   Ubuntu 14.04
Description:        
I need to access a temporary table created by a WITH statement in a function
called from the subsequent SELECT. I could not find any documentation
forbidding this behavior.
This is a self-contained example that should demonstrate the bug:
CREATE SCHEMA IF NOT EXISTS test;
SET SEARCH_PATH=test;
CREATE OR REPLACE FUNCTION test.func(table_name TEXT)
        RETURNS TABLE(id INTEGER) AS $$
        BEGIN
                EXECUTE 'CREATE TEMPORARY TABLE results AS (SELECT * FROM
'||table_name||')';
                RETURN QUERY(SELECT * FROM results);
        END;
        $$
LANGUAGE 'plpgsql';
WITH data AS
(
        SELECT * FROM generate_series(1,4)
)
SELECT * FROM test.func('data'); -- This errors out
DROP TABLE IF EXISTS dummy;
CREATE TABLE dummy(id INTEGER);
INSERT INTO dummy VALUES(1),(2),(3),(4),(5);
SELECT * FROM test.func('dummy'); -- This succeeds
| From | Date | Subject | |
|---|---|---|---|
| Next Message | pdasari | 2014-09-30 18:44:13 | BUG #11528: Max Index Keys | 
| Previous Message | Tom Lane | 2014-09-30 13:58:44 | Re: BUG #11523: Regular expressions work differently on different platforms |