Is there a simpler way to get the argument type list for a snowflake procedure?

user_15315543 0
Viewed 91

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!

snowflake-cloud-data-platform stored-procedures
2 Answers

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)
user_439920

I don't think there's a built-in way to do this. Here's an alternate way:

with A as
(
select PROCEDURE_NAME, split(replace(trim(ARGUMENT_SIGNATURE, '()'), ','), ' ') ARGS
      ,ARGUMENT_SIGNATURE
from   test.information_schema.procedures P
)
select PROCEDURE_NAME
       ,listagg(VALUE::string, ',') as ARGS
from A, table(flatten(ARGS))
where index % 2 = 1
group by PROCEDURE_NAME
;

You could also use a result scan after the show command to get the name of the procedure and argument signature in a single string:

show procedures;
select split("arguments", ')')[0] || ')' as SIGNATURE from table(result_scan(last_query_id()));
user_127563810
Related