Re: BUG #15515: DatabaseMetadata.getProcedures and getFunctionsreturn objects of either type - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15515
Logged by:          main street
Email address:      mainstreet439@gmail.com
PostgreSQL version: 11.1
Operating system:   windows
Description:       

When an application calls DatabaseMetadata.getFunctions  the Resultset
returned by the driver may include functions and vice versa re
DatabaseMetadata.getProcedures.

Using various PostregSQL JDBC drivers such as 42.2.5 against PostgresSQL 11
or pre-11 servers will return both types of objects.

In essence, we might expect the driver to treat a void return type as being
an indicator of a procedure versus a function if the pre-11 style of syntax
was used.
Using the new PostgresSQL 11 CREATE PROCEDURE syntax does not change the
responses.

Example objects

CREATE FUNCTION pinint(pint integer) RETURNS void
    LANGUAGE plpgsql
    AS $$
declare
        P1 integer;
begin
        P1:= PINT;
end;
$$;

CREATE FUNCTION pinint(pint integer) RETURNS void
    LANGUAGE plpgsql
    AS $$
declare
        P1 integer;
begin
        P1:= PINT;
end;
$$;

If you were to inspect the Resultset returned by
DatabaseMetadata.getProcedures you will see a response as shown.
https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getProcedures-java.lang.String-java.lang.String-java.lang.String-

[null],[dbcert],[finint],[null],[null],[null],[null],[2],[finint_16395]
[null],[dbcert],[pinint],[null],[null],[null],[null],[2],[pinint_16394]