> adrian.kla...@aklaver.com wrote: > >> Bryn wrote: >> >>> mmonc...@gmail.com wrote: >>> >>> SQL language functions have one clear advantage in that they can be inlined >>> in narrow contexts; this can give dramatic performance advantages when it >>> occurs. They have a lot of disadvantages: >>> >>> (1) Tables can’t be created then used without turning off function body >>> evaluation. >>> >>> (2) Queries must be parsed and planned upon each evocation (this can be >>> construed as advantage in scenarios where you want to float a function over >>> schemas). >>> >>> (3) Generally constrained to basic SQL statements (no variables, logic etc). >>> >>> …simplifies down to, “use SQL functions [only] when inlining”. >> >> About your point #1, I used a procedure to test this… I started with a >> working “language plpgsql” example… Then I changed it to a “language sql” >> test: >> >> drop table if exists t cascade; >> drop procedure if exists p() cascade; >> create procedure p() >> language sql >> as $body$ >> drop table if exists t cascade; >> create table t(k int primary key, v text not null); >> -- Causes compilation error: 42P01: relation "t" does not exist >> -- insert into t(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog'); >> $body$; >> >> With the “insert” in place, it fails the syntax check with the error that I >> mentioned. When it’s commented out, it passes the syntax check and executes >> without error and has the expected effect. > > Which is documented: > > https://www.postgresql.org/docs/current/xfunc-sql.html > <https://www.postgresql.org/docs/current/xfunc-sql.html> > > « > Note > > The entire body of an SQL function is parsed before any of it is executed. > While an SQL function can contain commands that alter the system catalogs > (e.g., CREATE TABLE), the effects of such commands will not be visible during > parse analysis of later commands in the function. Thus, for example, CREATE > TABLE foo (...); INSERT INTO foo VALUES(...); will not work as desired if > packaged up into a single SQL function, since foo won't exist yet when the > INSERT command is parsed. It's recommended to use PL/pgSQL instead of an SQL > function in this type of situation. > » > >> Bryn continued: >> >> The upshot of this is that I can’t design a test to demonstrate the effect >> that I thought you meant. Could you show me a code example, please? > > To turn off function body evaluation: > > https://www.postgresql.org/docs/current/runtime-config-client.html > > « > check_function_bodies (boolean) > > This parameter is normally on. When set to off, it disables validation of the > routine body string during CREATE FUNCTION and CREATE PROCEDURE. Disabling > validation avoids side effects of the validation process, in particular > preventing false positives due to problems such as forward references. Set > this parameter to off before loading functions on behalf of other users; > pg_dump does so automatically. > »
Thanks for those doc URLs, Adrian. Very helpful. I’ll do some serious reading and testing.
