Re: Why can't I have a "language sql" anonymous block?

> mmoncure(at)gmail(dot)com wrote:
>
>> Bryn wrote:
>>
>>> david(dot)g(dot)johnston(at)gmail(dot)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:

drop table if exists t cascade;
drop procedure if exists p() cascade;
create procedure p()
language plpgsql
as $body$
begin
drop table if exists t cascade;
create table t(k int primary key, v text not null);
insert into t(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog');
end;
$body$;

call p();
select k, v from t order by k;

This runs without error and produces the expected content in the newly-created table. This informs my understanding of the “compilation” that’s done at “create” time. It’s only a syntax check. If the check fails, then the “create” is turned into a no-op; else the source code is stored. Everything else happens at run time.

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.

This implies a different “create” model for a “language sql” unit than for a “language plsqsql” unit. This difference is described under “sql_body” in the PG doc section for “CREATE FUNCTION”—except that “sql_body” denotes an “unquoted” body and my example uses a “quoted” body. I tried the “unquoted” syntax thus:

create procedure p()
begin atomic
drop table if exists t cascade;
create table t(k int primary key, v text not null);
end;

But this caused the error “DROP TABLE is not yet supported in unquoted SQL function body”

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?

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
About your point #2…

I’m surprised by this.Consider this code example:

do $body$
begin
-- All because there's no "deallocate if exists".
deallocate v_from_t ;
exception
when invalid_sql_statement_name then null;
end;
$body$;

drop table if exists t cascade;
drop function if exists f_plpgsql(int) cascade;
drop function if exists f_quoted_sql(int) cascade;
drop function if exists f_unquoted_sql(int) cascade;

create table t(k int primary key, v text not null);
insert into t(k, v) values (1, 'dog'), (2, 'cat'), (3, 'frog');

prepare v_from_t(int) as
select t.v from t where t.k = $1;

create function f_plpgsql(k in int)
returns text
language plpgsql
as $body$
begin
return (select t.v from t where t.k = f_plpgsql.k);
end;
$body$;

create function f_quoted_sql(k in int)
returns text
language sql
as $body$
select t.v from t where t.k = f_quoted_sql.k;
$body$;

create function f_unquoted_sql(k in int) returns text
return (select t.v from t where t.k = f_unquoted_sql.k);

\set k 2
execute v_from_t(:k);
select f_plpgsql(:k);
select f_quoted_sql(:k);
select f_unquoted_sql(:k);

The “execute” and each “select” all give the same result.

I’ve come to assume that, at runtime, “execute v_from_t(:k)” has the same performance as “select f_plpgsql(:k)”—discounting the very first execution of the latter which implies the work of “prepare” too.

The prepare paradigm has the huge disadvantage that it must be done afresh in each newly-started session. But you can’t implement this in a trigger ‘cos (unlike Oracle Database) a PG developer can’t write an event trigger that fires when a session starts. In contrast, the PL/pgSQL function paradigm requires a single “create function” at install time; and thereafter, the “prepare” is done implicitly on first use in a new session.

Is my analysis here sound?

Moving on to “select f_quoted_sql(:k)”, the surprise is that this does NOT imply a “prepare” and that, rather, the “select… from t…” is compiled and executed from scratch of every use. Is this what you meant? If so, where is this documented. Having said this, if the body of “f_sql()” is simply inlined into any SQL that uses it, and if that invoking SQL is then prepared (either explicitly or because it’s used in the body of a “language plpgsql” unit, then all this boils down to nothing of concern.

Is the story different for “f_unquoted_sql()”?

Please clarify your point.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

I understand your point #3.