Re: Assert single row returning SQL-standard functions

Merlin Moncure <mmoncure@gmail.com> writes:
> How does that work in practice?  for current SQL (not pl/pgsql) functions,
> this will fail:

> create function f() returns int as $$ create temp table i(i int); select *
> from i; $$ language sql;
> ERROR:  relation "i" does not exist

Slightly off-topic: that example does actually work as of v18,
although you need to turn off check_function_bodies while
creating the function:

$ psql regression
psql (18beta3)
Type "help" for help.

regression=# create function f() returns int as $$ create temp table i(i int); select *
from i; $$ language sql;
ERROR:  relation "i" does not exist
LINE 2: from i; $$ language sql;
             ^
regression=# set check_function_bodies to off;
SET
regression=# create function f() returns int as $$ create temp table i(i int); select *
from i; $$ language sql;
CREATE FUNCTION
regression=# select f();
 f 
---
  
(1 row)

regression=# \d i
               Table "pg_temp_70.i"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 i      | integer |           |          | 


But David is correct that this is irrelevant to the case of
SQL-standard functions.  Everything mentioned in such a function has
to exist at function creation time, no exceptions.

There's a closely related complaint at [1], which I rather doubt
we're going to do anything about.

			regards, tom lane

[1] https://www.postgresql.org/message-id/19034-de0857b4f94ec10c%40postgresql.org