Re: anonymous block returning like a function

On 11/12/2020 21:06, PegoraroF10 wrote:
> I would like to have an anonymous block, like DO, but having resuts, like an
> usual function does.
>
> I know any user can do ...
>
> create function pg_temp.run_time_bigger(numeric,numeric) returns numeric
> language plpgsql as $$
> begin if $1 > $2 then return $1; else return $2; end if; end;$$;
> select * from pg_temp.run_time_bigger(5,3);
> drop function pg_temp.run_time_bigger(numeric,numeric);
>
> but would be better if he could ...
> execute block(numeric,numeric) returns numeric language plpgsql as $$
> begin if $1 > $2 then return $1; else return $2; end if; end;$$
> USING(5,3);
>
> That USING would be params, but if it complicates it could be easily be
> replaced by real values because that block is entirely created in run time,
> so its optional.
>
> What do you think about ?

Yeah, I think that would be useful. This was actually proposed and
discussed back in 2014 ([1], but it didn't lead to a patch. Not sure if
it's been discussed again after that.

> What part of postgres code do I have to carefully understand to write
> something to do that ?

Hmm, let's see. You'll need to modify the grammar in src/backend/gram.y,
to accept the USING clause. DoStmt struct needs a new 'params' field to
carry the params from the parser to the PL execution, I think you can
look at how that's done for ExecuteStmt or CallStmt for inspiration.
ExecuteDoStmt() needs some changes to pass the params to the 'laninline'
handler of the PL language. And finally, the 'laninline' implementations
of all the built-in languages needs to be modified to accept the
parameters, like plpgsql_compile_inline() function for PL/pgSQL. For
languages provided as extensions, there should be some mechanism to fail
gracefully, if the PL implementation hasn't been taught about the
parameters yet.

[1]
https://www.postgresql.org/message-id/1410849538.4296.19.camel%40localhost

- Heikki