Error on PRIVS: Functions/Procedures

[gabrielpiassetta]

I'm facing this error when trying to use the clone_schema function:

ERROR:  Action: PRIVS: Functions/Procedures  Diagnostics: line=PL/pgSQL function clone_schema(text,text,boolean,boolean) line 732 at EXECUTE. 42601. syntax error at or near "DEFAULT"
CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean,boolean) line 780 at RAISE

it comes from this generated query.

GRANT EXECUTE ON FUNCTION clone_schema_test_c.price_view_data (materialised_price_list_id integer, ofs integer, lim integer, filtering jsonb DEFAULT NULL::jsonb, sorting jsonb DEFAULT NULL::jsonb, flags jsonb DEFAULT NULL::jsonb) TO my_user;

which is generated by

SELECT 'GRANT ' || rp.privilege_type || ' ON ' || r.routine_type || ' ' || quote_ident(dest_schema) || '.' || rp.routine_name || ' (' || pg_get_function_arguments(p.oid) || ') TO ' || string_agg(distinct rp.grantee, ',') || ';' as func_dcl

for some reason, pg_get_function_arguments(p.oid) is generating an invalid sql?

[MichaelDBA]

well I think it's because the prototype header for the function definition itself includes DEFAULT keywords, but the function header for dropping or doing other actions on a function/procedure will barf if DEFAULT language is used.
CREATE FUNCTION whatever(akey integer default 0)... is OK
but
DROP FUNCTION whatever(akey integer default 0) wont work, neither GRANT on it with the default keywords

Let me work on this again...

[MichaelDBA]

ha, finally an easy one! There is another system func that returns the args WITHOUT DEFAULTS!

This is now fixed.

[gabrielpiassetta]

Great! now everything is working! thank you very much @MichaelDBA

[MichaelDBA]

Thank you, Gabriel, for helping to get these things cleaned up.  You always gave good info in your requests that made it easy to follow along.  Also, as I was fixing this stuff I added more SQL to the sampledb.sql file attached to this project for regression testing. Gabriel Piassetta wrote on 3/5/2021 10:52 AM:

…

Great! now everything is working! thank you very much @MichaelDBA <https://github.com/MichaelDBA> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#37 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC64IQOLQTJHE4KDEEGMMZ3TCD44FANCNFSM4YVKDOHA>.