Thread: Issue with GRANT/COMMENT ON FUNCTION with default
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
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
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.
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
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
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