Обсуждение: BUG #11526: WITH tables not accessible from function

Поиск
Список
Период
Сортировка

BUG #11526: WITH tables not accessible from function

От
bryan@unhwildhats.com
Дата:
The following bug has been logged on the website:

Bug reference:      11526
Logged by:          Bryan
Email address:      bryan@unhwildhats.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

Re: BUG #11526: WITH tables not accessible from function

От
Tom Lane
Дата:
bryan@unhwildhats.com writes:
> 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.

You're imagining that a WITH clause creates an actual table.  It does not,
any more than, say, a function call in the FROM clause does.  It's just
a name accessible within the query the WITH is attached to.  Sorry.

            regards, tom lane