po 14. 12. 2020 v 14:31 odesílatel Heikki Linnakangas <hlinnaka@iki.fi> napsal:
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.
Parametrization of DO statement can be first step and just this functionality can be pretty useful. Today, the code can be modification of CALL statement.
There should be discussion if DO statement will be more like procedure or more like function. Now, DO statement is more procedure than function. And I think so it is correct. Probably one day, the procedures can returns multirecordsets, and then can be easy same functionality to push to DO statement.
Oracle hace nice CTE enhancing
WITH FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS BEGIN RETURN p_id; END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1
Can be nice to have this feature in Postgres. We don't need to invite new syntax.
