Re: Issue with GRANT/COMMENT ON FUNCTION with default

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