From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
To: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Support for OUT parameters in procedures
Date: Mon, 5 Oct 2020 11:46:02 +0200
Message-ID: <d987da89-eafe-465e-f7c1-892c411a37d3@2ndquadrant.com> (raw)
In-Reply-To: <CAFj8pRCTiFr2wPQHtiz5G6OC-x5hstKEjvWZ+Uu7wZ4CWV9oTg@mail.gmail.com>
References: <2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com>
<4f733cca-5e07-e167-8b38-05b5c9066d04@2ndQuadrant.com>
<CAFj8pRCTiFr2wPQHtiz5G6OC-x5hstKEjvWZ+Uu7wZ4CWV9oTg@mail.gmail.com>
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).
> 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.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-jdbc@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, pavel.stehule@gmail.com, andrew.dunstan@2ndquadrant.com, pgsql-hackers@postgresql.org
Subject: Re: Support for OUT parameters in procedures
In-Reply-To: <d987da89-eafe-465e-f7c1-892c411a37d3@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox