Thread: Issue with GRANT/COMMENT ON FUNCTION with default

Issue with GRANT/COMMENT ON FUNCTION with default

From
Jim Nasby
Date:
Prior to default parameters on functions, GRANT and COMMENT accepted full parameter syntax. IE:

GRANT EXECUTE ON test(t text) TO public

as opposed to regprocedure, which only accepts the data types ( test(text), not test(t text) ).

They do not accept DEFAULT though:

GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
ERROR:  syntax error at or near "DEFAULT"
LINE 1: GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;

Presumably this is just an oversight? Related to that, is it intentional that the regprocedure cast disallows *any*
decoratorsto the function, other than type? If regprocedure at least accepted the full function parameter definition
youcould use it to get a definitive reference to a function.
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net



Re: Issue with GRANT/COMMENT ON FUNCTION with default

From
Alvaro Herrera
Date:
Jim Nasby wrote:
> Prior to default parameters on functions, GRANT and COMMENT accepted full parameter syntax. IE:
> 
> GRANT EXECUTE ON test(t text) TO public
> 
> as opposed to regprocedure, which only accepts the data types ( test(text), not test(t text) ).
> 
> They do not accept DEFAULT though:
> 
> GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
> ERROR:  syntax error at or near "DEFAULT"
> LINE 1: GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
> 
> Presumably this is just an oversight?

I have to say that accepting DEFAULT there seems pretty odd to me.  What
if you specify the wrong default?  Do you get a "no such function"
error?  That would be pretty unhelpful.  But then accepting it ignoring
the fact that the default is wrong would be rather strange.

> Related to that, is it intentional that the regprocedure cast
> disallows *any* decorators to the function, other than type? If
> regprocedure at least accepted the full function parameter definition
> you could use it to get a definitive reference to a function.

Does pg_identify_object() give you what you want?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services



Re: Issue with GRANT/COMMENT ON FUNCTION with default

From
Peter Eisentraut
Date:
On 5/5/14, 4:09 PM, Jim Nasby wrote:
> They do not accept DEFAULT though:
> 
> GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
> ERROR:  syntax error at or near "DEFAULT"
> LINE 1: GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
> 
> Presumably this is just an oversight?

It appears to be specified that way in SQL.  The DEFAULT clause is not
part of the function signature.




Re: Issue with GRANT/COMMENT ON FUNCTION with default

From
Jim Nasby
Date:
On 5/5/14, 3:22 PM, Alvaro Herrera wrote:
> Jim Nasby wrote:
>> Prior to default parameters on functions, GRANT and COMMENT accepted full parameter syntax. IE:
>>
>> GRANT EXECUTE ON test(t text) TO public
>>
>> as opposed to regprocedure, which only accepts the data types ( test(text), not test(t text) ).
>>
>> They do not accept DEFAULT though:
>>
>> GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
>> ERROR:  syntax error at or near "DEFAULT"
>> LINE 1: GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
>>
>> Presumably this is just an oversight?
>
> I have to say that accepting DEFAULT there seems pretty odd to me.  What
> if you specify the wrong default?  Do you get a "no such function"
> error?  That would be pretty unhelpful.  But then accepting it ignoring
> the fact that the default is wrong would be rather strange.

We already have that exact problem with the name of the argument.

decibel@decina.cashnetusa=# CREATE FUNCTION test(t text default '') RETURNS text LANGUAGE sql AS 'SELECT $1';
CREATE FUNCTION
decibel@decina.cashnetusa=# GRANT EXECUTE ON FUNCTION test(baz text) to public;
GRANT
decibel@decina.cashnetusa=#

>> Related to that, is it intentional that the regprocedure cast
>> disallows *any* decorators to the function, other than type? If
>> regprocedure at least accepted the full function parameter definition
>> you could use it to get a definitive reference to a function.
>
> Does pg_identify_object() give you what you want?

No, because I'd need an OID, and I have no way of reliably getting that because the regprocedure cast won't even accept
additionalinformation beyond data type. 
--
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net




Re: Issue with GRANT/COMMENT ON FUNCTION with default

From
Jim Nasby
Date:
On 5/6/14, 1:57 PM, Peter Eisentraut wrote:
> On 5/5/14, 4:09 PM, Jim Nasby wrote:
>> They do not accept DEFAULT though:
>>
>> GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
>> ERROR:  syntax error at or near "DEFAULT"
>> LINE 1: GRANT EXECUTE ON FUNCTION test(t text DEFAULT '') to public;
>>
>> Presumably this is just an oversight?
>
> It appears to be specified that way in SQL.  The DEFAULT clause is not
> part of the function signature.

As in per-ANSI?

Let me ask a related question: is there any reason why we don't accept argument names or the default specifier in the
regprocedurecast?
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net



Re: Issue with GRANT/COMMENT ON FUNCTION with default

From
Tom Lane
Date:
Jim Nasby <jim@nasby.net> writes:
> Let me ask a related question: is there any reason why we don't accept argument names or the default specifier in the
regprocedurecast?
 

regprocedure_in does not contain a full SQL parser, which it would pretty
much need in order to deal with default expressions.  Argument names it
could possibly ignore without much trouble, but nobody bothered to teach
it about that.
        regards, tom lane