Horton

Re: Assert single row returning SQL-standard functions

Merlin Moncure <mmoncure@gmail.com>

View thread
From: Merlin Moncure <mmoncure@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Joel Jacobson <joel@compiler.org>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-08-29T19:12:50Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add error_on_null(), checking if the input is the null value

  2. SQL-standard function body

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

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

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
>