Re: Assert single row returning SQL-standard functions

Merlin Moncure <mmoncure(at)gmail(dot)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