From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
Cc: | Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Support for OUT parameters in procedures |
Date: | 2020-10-05 11:39:05 |
Message-ID: | CAFj8pRAestyCA8P4mEisY-bYO3orJMgCG5uo2XgciW9EfZwAbg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-jdbc |
po 5. 10. 2020 v 11:46 odesílatel Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> napsal:
> On 2020-09-29 08:23, Pavel Stehule wrote:
> > This was an important issue if I remember well. Passing mandatory NULL
> > as OUT arguments solves this issue.
> > I fully agree so OUT arguments are part of the procedure's signature.
> > Unfortunately, there is another difference
> > from functions, but I don't think so there is a better solution, and we
> > should live with it. I think it can work well.
>
> This has been committed.
>
> > I found one issue. The routine for selecting function or procedure based
> > on signature should be fixed.
> >
> > CREATE OR REPLACE PROCEDURE public.procp(OUT integer)
> > LANGUAGE plpgsql
> > AS $procedure$
> > BEGIN
> > $1 := 10;
> > END;
> > $procedure$
> >
> > DO
> > $$
> > DECLARE n numeric;
> > BEGIN
> > CALL procp(n);
> > RAISE NOTICE '%', n;
> > END;
> > $$;
> > ERROR: procedure procp(numeric) does not exist
> > LINE 1: CALL procp(n)
> > ^
> > HINT: No procedure matches the given name and argument types. You might
> > need to add explicit type casts.
> > QUERY: CALL procp(n)
> > CONTEXT: PL/pgSQL function inline_code_block line 4 at CALL
>
> This is normal; there is no implicit cast from numeric to int. The same
> error happens if you call a function foo(int) with foo(42::numeric).
>
this is OUT argument - so direction is reversed - and implicit cast from
int to numeric exists.
> > postgres=# create or replace procedure px(anyelement, out anyelement)
> > as $$
> > begin
> > $2 := $1;
> > end;
> > $$ language plpgsql;
> >
> > postgres=# call px(10, null);
> > ERROR: cannot display a value of type anyelement
> >
> > but inside plpgsql it works
> > do $$
> > declare xx int;
> > begin
> > call px(10, xx);
> > raise notice '%', xx;
> > end;
> > $$;
>
> This might be worth further investigation, but since it happens also
> with INOUT parameters, it seems orthogonal to this patch.
>
yes - this breaks using varchar against text argument, although these types
are almost identical.
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2020-10-05 11:50:26 | Re: [Patch] Optimize dropping of relation buffers using dlist |
Previous Message | Heikki Linnakangas | 2020-10-05 11:36:39 | Re: Online checksums patch - once again |
From | Date | Subject | |
---|---|---|---|
Next Message | Steven Schlansker | 2020-10-05 16:16:54 | Re: Support for OUT parameters in procedures |
Previous Message | Dave Cramer | 2020-10-05 10:54:23 | Fwd: Support for OUT parameters in procedures |