Add notification on BEGIN ATOMIC SQL functions using temp relations

Hi,

While reviewing a patch I noticed that SQL functions defined with BEGIN
ATOMIC can reference temporary relations, and such functions are
(rightfully) dropped at session end --- but without any notification to
the user:

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1

postgres=# CREATE FUNCTION tmpval_atomic()
RETURNS int LANGUAGE sql
BEGIN ATOMIC;
SELECT val FROM tmp;
END;
CREATE FUNCTION

postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------+------------------+---------------------+------
public | tmpval_atomic | integer | | func
(1 row)

postgres=# \q

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

Although this behaviour is expected, it can be surprising. A NOTICE or
WARNING at CREATE FUNCTION time could save some head-scratching later.
We already have a precedent. When creating a view that depends on a
temporary relation, postgres automatically makes it a temporary view and
emits a NOTICE:

postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1

postgres=# CREATE VIEW v AS SELECT * FROM tmp;
NOTICE: view "v" will be a temporary view
CREATE VIEW

postgres=# \d
List of relations
Schema | Name | Type | Owner
------------+------+-------+-------
pg_temp_74 | tmp | table | jim
pg_temp_74 | v | view | jim
(2 rows)

postgres=# \q

$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.

postgres=# \d
Did not find any relations.

Attached a PoC that issues a WARNING if a BEGIN ATOMIC function is
created using temporary objects:

postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1

postgres=# CREATE FUNCTION tmpval_atomic()
RETURNS int LANGUAGE sql
BEGIN ATOMIC;
SELECT val FROM tmp;
END;
WARNING: function defined with BEGIN ATOMIC depends on temporary
relation "tmp"
DETAIL: the function will be dropped automatically at session end.
CREATE FUNCTION

This PoC adds a parameter to check_sql_fn_statements() and
check_sql_fn_statement(), so I’m not entirely sure if that’s the best
approach. I’m also not sure whether a NOTICE would be a better fit than
a WARNING here. Feedback is welcome.

Any thoughts?

Best regards, Jim