Обсуждение: anonymous block returning like a function

Поиск
Список
Период
Сортировка

anonymous block returning like a function

От
PegoraroF10
Дата:
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 ? 
What part of postgres code do I have to carefully understand to write
something to do that ?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html



Re: anonymous block returning like a function

От
Heikki Linnakangas
Дата:
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



Re: anonymous block returning like a function

От
Pavel Stehule
Дата:


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.

[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