On Sun, Nov 3, 2024 at 10:54 PM Alexander Korotkov <aekorot...@gmail.com> wrote: > > On Mon, Oct 28, 2024 at 9:42 PM Alexander Korotkov <aekorot...@gmail.com> > wrote: > > > > On Mon, Oct 28, 2024 at 11:36 AM Heikki Linnakangas <hlinn...@iki.fi> wrote: > > > > > > On 25/10/2024 14:56, Alexander Korotkov wrote: > > > > I see that pg_wal_replay_wait_status() might look weird, but it seems > > > > to me like the best of feasible solutions. > > > > > > I haven't written many procedures, but our docs say: > > > > > > > Procedures do not return a function value; hence CREATE PROCEDURE > > > lacks a RETURNS clause. However, procedures can instead return data to > > > their callers via output parameters. > > > > > > Did you consider using an output parameter? > > > > Yes I did consider them and found two issues. > > 1) You still need to pass something to them. And that couldn't be > > default values. That's a bit awkward. > > 2) Usage of them causes extra snapshot to be held. > > I'll recheck if it's possible to workaround any of these two. > > I've rechecked the output parameters for stored procedures. And I think the > behavior I previously discovered is an anomaly. > > CREATE PROCEDURE test_proc(a integer, out b integer) > LANGUAGE plpgsql > AS $$ > BEGIN > b := a; > END; > $$; > > # call test_proc(1); > ERROR: procedure test_proc(integer) does not exist > LINE 1: call test_proc(1); > ^ > HINT: No procedure matches the given name and argument types. You might need > to add explicit type casts. > > # call test_proc(1,2); > b > --- > 1 > (1 row) > > Looks weird that we have to pass in some (ignored?) values for output > parameters. In contrast, functions don't require this. > > CREATE FUNCTION test_func(a integer, out b integer) > LANGUAGE plpgsql > AS $$ > BEGIN > b := a; > END; > $$; > > # select test_func(1); > test_func > ----------- > 1 > (1 row) > > This makes me think we have an issue with stored procedures here. I'll try > to investigate it further.
Oh, this seems to be intentional [1] and seems to be part of standard [2]. Links 1. https://www.postgresql.org/docs/devel/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS-PROC 2. https://www.postgresql.org/message-id/2b8490fe-51af-e671-c504-47359dc453c5%402ndquadrant.com ------ Regards, Alexander Korotkov Supabase
