public inbox for [email protected]
help / color / mirror / Atom feedPossible mismatch between behaviour and documentation in CREATE FUNCTION
2+ messages / 2 participants
[nested] [flat]
* Possible mismatch between behaviour and documentation in CREATE FUNCTION
@ 2026-04-11 03:40 Isaac Morland <[email protected]>
2026-04-11 03:53 ` Re: Possible mismatch between behaviour and documentation in CREATE FUNCTION David G. Johnston <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Isaac Morland @ 2026-04-11 03:40 UTC (permalink / raw)
To: PostgreSQL Developers <[email protected]>
The documentation says that only BEGIN ATOMIC parses the function body at
definition time. Whereas AS parses the function body at execution time:
https://www.postgresql.org/docs/current/sql-createfunction.html
(and look at the explanation of sql_body)
For PLPGSQL this works the way I would expect:
postgres=# create or replace function t (f int) returns void language
plpgsql as $$ begin select abs (f); end; $$;
CREATE FUNCTION
postgres=# create or replace function t (f int) returns void language
plpgsql as $$ begin select absasdf (f); end; $$;
CREATE FUNCTION
But for SQL it does not - it's pretty clear some pretty detailed checking
of the function body is happening:
postgres=# create or replace function t (f int) returns void language sql
as $$ select abs (f); $$;
CREATE FUNCTION
postgres=# create or replace function t (f int) returns void language sql
as $$ select absasdf (f); $$;
ERROR: function absasdf(integer) does not exist
LINE 1: ... t (f int) returns void language sql as $$ select absasdf (f...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
The only difference between the last two statements is whether or not the
specified function actually exists.
The other thing I don't understand is how this interacts with the behaviour
of search_path. My understanding was that the function behaviour could
change depending on the search_path at call time; but if that's true it's
nonsensical to parse (or at least to look up objects used by) the function
at definition time, because the meaning of the body depends on the
search_path and could be valid at execution time even if not at definition
time.
If we talk about overall syntax, not just things like function name lookup,
then my confusion extends to PLPGSQL as well:
postgres=# create or replace function t (f int) returns void language
plpgsql as $$ begin select; end; $$;
CREATE FUNCTION
postgres=# create or replace function t (f int) returns void language
plpgsql as $$ begin selec; end; $$;
ERROR: syntax error at or near "selec"
LINE 1: ...(f int) returns void language plpgsql as $$ begin selec; end...
^
In other words, while PLPGSQL doesn't look up function names to check that
they exist, it won't accept a syntactically invalid function body.
I feel that I must somehow be confusing myself, because the documentation
describes how I thought the system worked but I don't see how that can be
reconciled with the observed behaviour. Do we need a documentation update?
This is all on a reasonably recent version:
postgres=# select version ();
version
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
PostgreSQL 18.1 (Homebrew) on x86_64-apple-darwin23.6.0, compiled by Apple
clang version 16.0.0 (clang-1600.0.26.6), 64-bit
(1 row)
Thanks for any insight anybody can provide.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Possible mismatch between behaviour and documentation in CREATE FUNCTION
2026-04-11 03:40 Possible mismatch between behaviour and documentation in CREATE FUNCTION Isaac Morland <[email protected]>
@ 2026-04-11 03:53 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2026-04-11 03:53 UTC (permalink / raw)
To: Isaac Morland <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>
On Friday, April 10, 2026, Isaac Morland <[email protected]> wrote:
> The documentation says that only BEGIN ATOMIC parses the function body at
> definition time. Whereas AS parses the function body at execution time:
>
> https://www.postgresql.org/docs/current/sql-createfunction.html
>
> (and look at the explanation of sql_body)
>
Possibly some documentation tweaks are in order. I haven’t looked at it in
depth. The optional checks are able to be disabled:
https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT
While the result of the required parsing of a function body is implying
that such parsing is saved to the catalogs so that creation-time object
references are retained. While the others are re-parsed during execution
and object references retained only for the lifetime of the query.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-11 03:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-11 03:40 Possible mismatch between behaviour and documentation in CREATE FUNCTION Isaac Morland <[email protected]>
2026-04-11 03:53 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox