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