Thread: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

[BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

From

ppoojari@erwin.com

Date:

15 February 2017, 01:11:01

The following bug has been logged on the website:

Bug reference:      14545
Logged by:          Prajval Poojari
Email address:      ppoojari@erwin.com
PostgreSQL version: 9.5.6
Operating system:   Windows 10 Pro
Description:

Hi
i ran a simple select query as
"select pa.parameter_mode from information_schema as pa where
pa.parameter_mode = 'VARIADIC' "
and the DB returned me a null list.
I had already created a function with the parameter mode as VARIADIC.
Just wondering if there is some other specific way to retrieve this mode.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

From

"David G. Johnston"

Date:

15 February 2017, 03:57:51

On Tue, Feb 14, 2017 at 12:11 PM, <ppoojari@erwin.com> wrote:

The following bug has been logged on the website:

Bug reference: 14545
Logged by: Prajval Poojari
Email address: ppoojari@erwin.com
PostgreSQL version: 9.5.6
Operating system: Windows 10 Pro
Description:

Hi
i ran a simple select query as
"select pa.parameter_mode from information_schema as pa where
pa.parameter_mode = 'VARIADIC' "
and the DB returned me a null list.
I had already created a function with the parameter mode as VARIADIC.
Just wondering if there is some other specific way to retrieve this mode.

I just get an error when I try that - I think you are missing the "parameters" table specification.

CREATE FUNCTION variadictest(VARIADIC vargs text[])

RETURNS text

AS $$

SELECT 'test'::text;

LANGUAGE sql;

# select data_type from information_schema.parameters WHERE specific_name ~ 'variadictest';

-> "ARRAY"

While imperfect locating any ARRAY data_type argument positioned last would be accurate if potentially imprecise (i.e., possible false positives).

There may be (probably is) a PostgreSQL-specific catalog that will contain the desired level of detail. Information Schema is "least common denominator" in nature which may explain the lack of specificity here.

David J.

Re: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

From

Prajval Poojari

Date:

15 February 2017, 04:15:33

HI David,

Sorry I made typo error while logging the bug.

I executed this test script

CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$

SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);

$$ LANGUAGE SQL;

Then

I executed a test retrieve query as

select distinct pa.parameter_mode

from information_schema.parameters as pa

o/p

"OUT"

"IN"

Example 2

I executed a test retrieve query as

select distinct pa.parameter_mode

from information_schema.parameters as pa

where pa.parameter_mode = 'VARIADIC'

o/p

Empty result set

I believed if other parameter modes are getting retrieved in the o.p same way ‘VARIADIC’ should be retrieved which is not the case.

So I believe it to be bug or partial implementation of the parameter_mode feature

Please let me know your comments

Regards,

Prajval Poojari

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, February 14, 2017 4:58 PM
To: Prajval Poojari <ppoojari@erwin.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

On Tue, Feb 14, 2017 at 12:11 PM, <ppoojari@erwin.com> wrote:

The following bug has been logged on the website:

Bug reference: 14545
Logged by: Prajval Poojari
Email address: ppoojari@erwin.com
PostgreSQL version: 9.5.6
Operating system: Windows 10 Pro
Description:

Hi
i ran a simple select query as
"select pa.parameter_mode from information_schema as pa where
pa.parameter_mode = 'VARIADIC' "
and the DB returned me a null list.
I had already created a function with the parameter mode as VARIADIC.
Just wondering if there is some other specific way to retrieve this mode.

I just get an error when I try that - I think you are missing the "parameters" table specification.

CREATE FUNCTION variadictest(VARIADIC vargs text[])

RETURNS text

AS $$

SELECT 'test'::text;

LANGUAGE sql;

# select data_type from information_schema.parameters WHERE specific_name ~ 'variadictest';

-> "ARRAY"

While imperfect locating any ARRAY data_type argument positioned last would be accurate if potentially imprecise (i.e., possible false positives).

David J.

Re: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

From

"David G. Johnston"

Date:

15 February 2017, 04:43:48

On Tue, Feb 14, 2017 at 3:15 PM, Prajval Poojari <ppoojari@erwin.com> wrote:

Please let me know your comments

I don't have access to an authoritative definition for what the allowed values for information_schema.parameters.parameter_mode are.

The fact that our "VARIADIC" mode maps to "IN" on the information schema is not accidental. The question is whether it is out-of-date. Tom Lane committed that particular change back in 2008 (d89737d3) though the file header suggests it conforms to the ISO/IEC 9075-11:2011 specification.

Is there a reason you don't just use the PostgreSQL catalogs? Are you aware of other implementations of information schema that have this level of detail?

Changing the value from "IN" to "VARIADIC" would be a breaking change for systems that only care about the "IN" property of the argument. Even matching the standard on this topic might not be warranted given 9 years of the present behavior. Without even that benefit the hurdle to change this is extremely high - especially when pg_proc is authoritative from our internal perspective.

David J.

Re: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

From

Prajval Poojari

Date:

15 February 2017, 04:58:45

Hey David,

I don’t want to change the “VARIADIC” mode to “IN”, I just wanted a sql query to retrieve the parameter mode “VARIADIC”.

As per your suggestion I I will use pg_proc it has the implementation needed for me.

Regards,

Prajval Poojari

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, February 14, 2017 5:44 PM
To: Prajval Poojari <ppoojari@erwin.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

On Tue, Feb 14, 2017 at 3:15 PM, Prajval Poojari <ppoojari@erwin.com> wrote:

Please let me know your comments

I don't have access to an authoritative definition for what the allowed values for information_schema.parameters.parameter_mode are.

Is there a reason you don't just use the PostgreSQL catalogs? Are you aware of other implementations of information schema that have this level of detail?

David J.

Re: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

From

Tom Lane

Date:

15 February 2017, 05:10:53

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I don't have access to an authoritative definition for what the allowed
> values for information_schema.parameters.parameter_mode are.

SQL2011 saith

  CONSTRAINT PARAMETER_MODE_CHECK
    CHECK (
       PARAMETER_MODE IN
       ( 'IN', 'OUT', 'INOUT' ) ),

> The fact that our "VARIADIC" mode maps to "IN" on the information schema is
> not accidental.  The question is whether it is out-of-date.

Not that I can see.  SQL doesn't have VARIADIC, so there's no way to
represent that a parameter is variadic within the information_schema.

This is generally true for all sorts of nonstandard features that PG has:
there's just no way to find out about them through the information_schema,
and there isn't going to be because making those views behave in
nonstandard ways would largely defeat the point of having them.

> Is there a reason you don't just use the PostgreSQL catalogs?  Are you
> aware of other implementations of information schema that have this level
> of detail?

mysql, at least, feels free to extend the information_schema in
nonstandard ways.  Our project policy is that they're doing it wrong.

            regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #14545: Unable to retrieve parameter mode 'VARIADIC'

From

"David G. Johnston"

Date:

15 February 2017, 05:17:41

On Tue, Feb 14, 2017 at 4:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I don't have access to an authoritative definition for what the allowed
> values for information_schema.parameters.parameter_mode are.

SQL2011 saith

CONSTRAINT PARAMETER_MODE_CHECK
CHECK (
PARAMETER_MODE IN
( 'IN', 'OUT', 'INOUT' ) ),

> The fact that our "VARIADIC" mode maps to "IN" on the information schema is
> not accidental. The question is whether it is out-of-date.

Not that I can see. SQL doesn't have VARIADIC, so there's no way to
represent that a parameter is variadic within the information_schema.

Yeah, that is covered indirectly by:

"A CREATE FUNCTION command is defined in SQL:1999 and later. The PostgreSQL version is similar but not fully compatible."

https://www.postgresql.org/docs/9.5/static/sql-createfunction.html

But VARIADIC is not specifically mentioned as being non-standard anywhere I stumbled across so getting a definitive answer from the docs wasn't possible.

David J.