On 12/15/21 13:05, Bryn Llewellyn wrote:
mmoncure@xxxxxxxxx <mailto:mmoncure@xxxxxxxxx> wrote:
Bryn wrote:
david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx> wrote:
Bryn wrote:
There must be a reason to prefer a “language sql” procedure over a
“language plpgsql” procedure—otherwise the former wouldn’t be
supported.
I would say that is true for functions. I wouldn’t assume that for
procedures—it’s probable that because sql already
worked for functions we got that feature for free when implementing
procedures.
Interesting. That’s exactly the kind of historical insight I was
after. Thanks.
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 because functions shouldn’t do DDL. I
started with a working “language plpgsql” example:
Since procedures are relatively new to Postgres you are going to find
more functions doing DDL then procedures. Not sure I follow why one is
preferred over the other anyway?
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
"
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.
"
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.
"
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
