RE: BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB

<div class="socmaildefaultfont" dir="ltr" style="font-family:Arial, Helvetica, sans-serif;font-size:10pt" ><div dir="ltr" ><br>Hi Dave,<br>&nbsp;
<div>We are using 42.2.11 and the latest JDBC driver. We also tried 42.2.16 JDBC drivers as well.</div><br>please note that getProcedures() returns nothing, which is expected as we do not have any Stored procedure.<br><br>but when we use getProcedureColumns(catalog, schema,null,null) then it returns the columns from Functions, which is unexpected and is a bug.<br><br>Thanks&nbsp;<br>Adarshdeep<br><br><br>----- Original message -----<br>From: Dave Cramer &lt;davecramer(at)postgres(dot)rocks&gt;<br>To: adarshdeep(dot)cheema(at)ibm(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org<br>Cc:<br>Subject: [EXTERNAL] Re: BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB<br>Date: Tue, Sep 1, 2020 6:36 PM<br>&nbsp;
<div dir="ltr" >This has been fixed in
<div><a href="https://github.com/pgjdbc/pgjdbc/pull/1723" target="_blank">https://github.com/pgjdbc/pgjdbc/pull/1723</a></div>
<div>Please upgrade the driver&nbsp;
<div><div data-smartmail="gmail_signature" dir="ltr" ><div dir="ltr" >Dave Cramer
<div>www.postgres.rocks</div></div></div></div></div></div>&nbsp;

<div><div dir="ltr" >On Mon, 27 Jul 2020 at 13:47, PG Bug reporting form &lt;<a href="mailto:noreply(at)postgresql(dot)org" target="_blank">noreply(at)postgresql(dot)org</a>&gt; wrote:</div>
<blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" >The following bug has been logged on the website:<br><br>Bug reference:&nbsp; &nbsp; &nbsp; 16557<br>Logged by:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Adarshdeep Cheema<br>Email address:&nbsp; &nbsp; &nbsp; <a href="mailto:adarshdeep(dot)cheema(at)ibm(dot)com" target="_blank">adarshdeep(dot)cheema(at)ibm(dot)com</a><br>PostgreSQL version: 12.2<br>Operating system:&nbsp; &nbsp;Windows<br>Description:&nbsp; &nbsp; &nbsp; &nbsp;<br><br>Create any user defined function using Postgres 12.3 Server, we are using<br>postgresql-42.2.11 JDBC driver<br><br>DDLs:<br>CREATE FUNCTION findec(pdec numeric) RETURNS numeric<br>&nbsp; &nbsp; LANGUAGE plpgsql<br>&nbsp; &nbsp; AS $$<br>declare r decimal(7,2);<br>begin<br>&nbsp; &nbsp; &nbsp; &nbsp; r = PDEC;<br>&nbsp; &nbsp; &nbsp; &nbsp; return r;<br>end;<br>$$;<br><br><br>a) When we use the following Java code, then we get nothing as we do no have<br>any Stored Procedure defined, which is expected<br>&nbsp; resultSet= metadata.getProcedures(null , "public", null); ,<br>&nbsp; while (resultSet.next()){<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(1)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(2)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(3)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(4)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(5)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(resultSet.getString(6)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; }<br><br>b) Now change the JAVA code to the following and you will get two columns in<br>the resultSet, which is a bug as there is no StoredProcedure Defined in the<br>database<br><br>&nbsp;resultSet= metadata.getProcedureColumns(null , "public", "findec", null);<br>,<br>&nbsp; while (resultSet.next()){<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(1)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(2)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(3)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(4)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.print(resultSet.getString(5)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println(resultSet.getString(6)+"&nbsp; &nbsp; ");<br>&nbsp; &nbsp; &nbsp; &nbsp; }<br><br><br>OUTPUT:<br>null&nbsp; &nbsp; public&nbsp; &nbsp; findec&nbsp; &nbsp; returnValue&nbsp; &nbsp; 5&nbsp; &nbsp; 2&nbsp; &nbsp;<br>null&nbsp; &nbsp; public&nbsp; &nbsp; findec&nbsp; &nbsp; pdec&nbsp; &nbsp; 1&nbsp; &nbsp; 2<br>&nbsp;</blockquote></div></div>
<div dir="ltr" >&nbsp;</div></div><BR>