I need to transfer ownership of snowflake procedures post clone to a new Role.
To do this I'm using a procedure which works through all objects from the database.information_schema.xxxx views.
The procedures are problematic though, the SHOW PROCEDURE has a column which shows the procedure signature as just argument types, but the information_schema.procedures view shows the actual parameter name as well as its argument type, which if passed into a GRANT command does not work - the grant expects the Argument Type signature only, not the parameter names :/
SHOW PROCEDURE ARGUMENTS => PROCEDURE_NAME(VARCHAR) RETURN VARCHAR INFORMATION_SCHEMA.PROCEDURES.ARGUMENT_SIGNATURE => PROCEDURE_NAME(P_PARAM1 VARCHAR)
I eventually came upwith this which was fun, but feels rather complicated, the question is - have I missed a simpler approach?
SELECT procedure_name
, concat('(',listagg(argtype, '') within group (order by argindex)) cleanArgTypes
FROM (SELECT procedure_name
, argument_signature
, lf.index argindex
, lf.value argtype
FROM rock_dev_test_1.information_schema.procedures
, lateral flatten(input=>split(decode(argument_signature
,'()','( )'
,argument_signature
),' ')
,outer=>true) lf
WHERE lf.index/2 != round(lf.index/2)
)
GROUP BY procedure_name
, argument_signature
ORDER by 1,2;
cleanArgTypes => (VARCHAR)
This takes the overspecific argument_signature splits it into an array using space as a delimiter, then laterally flatten the return set into rows, discard the parameter names (always at an even index) then groups by parameter name and signature and uses ListAgg to put the parameter argument types back into a string.
Small wrinkle in that () doesn't work, so has to be shifted to ( )
Whilst I enjoyed dabbling with some of Snowflakes Semi-structured capabilities, If there was a simpler approach I'd rather use it!
Mostly the same code, but it doesn't need to be nested, I swapped from the arg_sig (the input) to using the SEQ of the split, but mostly the same still:
SELECT p.procedure_name
,'( '|| listagg(split_part(trim(t.value),' ',2), ', ') within group (order by t.index) || ')' as out
FROM information_schema.procedures as p
,table(split_to_table(substring(p.argument_signature, 2,length(p.argument_signature)-2), ',')) t
group by 1, t.seq;
for the toy procedures in my stack overflow schema I get:
PROCEDURE_NAME | OUT |
---|---|
DATE_HANDLER | ( DATE) |
TODAYS_DELIVERY_AMOUNT | ( VARCHAR) |
ABC | ( TIMESTAMP_NTZ, TIMESTAMP_NTZ, VARCHAR) |
ABC_DAILY | ( ) |
STRING_HANDLER | ( VARCHAR) |