From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bernice.southey@gmail.com
Subject: BUG #19072: New-style SQL language function referencing a temp table behaves oddly
Date: Sat, 04 Oct 2025 16:51:33 +0000
Message-ID: <19072-819bab455efebdd7@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19072
Logged by: Bernice Southey
Email address: bernice.southey@gmail.com
PostgreSQL version: 18.0
Operating system: Linux Mint 22.2
Description:
When created in psql, a new-style SQL language function referencing a temp
table disappears with the session, as if it were a temporary function.
When created in pgAdmin, the function survives. It always succeeds even if
the temp table doesn't exist, and if it does, its row are ignored.
It makes sense that new-style SQL language functions don't support temp
tables, but the current non-support is somewhat confusing.
I couldn't find any references to this, my apologies if it's already known.
VERSIONS:
Server: PostgreSQL 18.0 (Ubuntu 18.0-1.pgdg24.04+3) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
psql: 18.0 (Ubuntu 18.0-1.pgdg24.04+3)
pgAdmin: 9.8
Reproducible on a PostgreSQL 17 server.
STEPS TO REPRODUCE
---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 1: In psql create a temporary table and a new-style SQL function
referencing the table.
---------------------------------------------------------------------------------------------------------------------------------------------------
:~$ psql -d postgresql://postgres:@localhost/test
test=# CREATE TEMP TABLE test(i) AS VALUES(1); CREATE FUNCTION vanish()
RETURNS INT RETURN (SELECT i FROM test); SELECT vanish();
SELECT 1
CREATE FUNCTION
vanish
--------
1
(1 row)
---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 2: Start a new session and select the function. The function does not
exist.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# \q
:~$ psql -d postgresql://postgres:@localhost/test
test=# SELECT vanish();
ERROR: function vanish() does not exist
LINE 1: SELECT vanish();
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 3: In pgAmin create a temp table and a new-style SQL function
referencing the table. (as per step 1)
---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TEMP TABLE test(i) AS VALUES(1); CREATE FUNCTION vanish() RETURNS INT
RETURN (SELECT i FROM test); SELECT vanish();
---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 4: In psql select the function. It should error because the table
doesn't exist but it returns no rows.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# SELECT vanish();
vanish
--------
(1 row)
---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 5: Create the temp table with rows and call the function. It returns no
rows.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# CREATE TEMP TABLE test AS VALUES(2); SELECT vanish();
SELECT 1
vanish
--------
(1 row)
---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 6: For completeness create the temp table and function with quoted SQL
language syntax.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# CREATE TEMP TABLE test2(i) AS VALUES(3); CREATE FUNCTION vanish2()
RETURNS INT LANGUAGE SQL AS $$SELECT i FROM test2$$; SELECT vanish2();
SELECT 1
CREATE FUNCTION
vanish2
---------
3
(1 row)
---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 7: Start a new session and select the function. As expected the
function exists and the table does not.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# \q
:~$ psql -d postgresql://postgres:@localhost/test
test=# SELECT vanish2();
ERROR: relation "test2" does not exist
LINE 1: SELECT i FROM test2
^
QUERY: SELECT i FROM test2
CONTEXT: SQL function "vanish2" during inlining
test=#
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-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, bernice.southey@gmail.com
Subject: Re: BUG #19072: New-style SQL language function referencing a temp table behaves oddly
In-Reply-To: <19072-819bab455efebdd7@postgresql.org>
* 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