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(at)decina(dot)cashnetusa=# CREATE FUNCTION test(t text default '') RETURNS text LANGUAGE sql AS 'SELECT $1';
CREATE FUNCTION
decibel(at)decina(dot)cashnetusa=# GRANT EXECUTE ON FUNCTION test(baz text) to public;
GRANT
decibel(at)decina(dot)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 additional information beyond data type.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net