BUG #19072: New-style SQL language function referencing a temp table behaves oddly

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=#