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.
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?
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.
