Re: Assert single row returning SQL-standard functions

On Fri, Aug 29, 2025 at 12:07 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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:
>

Right, thanks. The veiled note was this behavior specifically is not
uniformly better, given that pl/pgsql can be utilized to work around it. I
have a lot more questions regarding standards compatibility coming to
procedures, but not for this thread.

merlin

>
> $ 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
>