BUG #11526: WITH tables not accessible from function

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-bugs by date

  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