Re: anonymous block returning like a function

po 14. 12. 2020 v 14:31 odesílatel Heikki Linnakangas <hlinnaka(at)iki(dot)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.
>
> [1]
> https://www.postgresql.org/message-id/1410849538.4296.19.camel%40localhost

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.

Regards

Pavel

>
> - Heikki
>
>
>