Re: Why can't I have a "language sql" anonymous block? - Mailing list pgsql-general

On 12/15/21 13:05, Bryn Llewellyn wrote:
>> mmoncure@gmail.com <mailto:mmoncure@gmail.com> wrote:
>>
>>> Bryn wrote:
>>>
>>>> david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> 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@aklaver.com