To pass key value pairs to a procedure, you can use multi-dimensional arrays - here's an example:
BEGIN DECLARE object params = ARRAY( ARRAY( 'a' , 'b' ) , ARRAY( 'c' , 'd' ) ); DECLARE integer VARIABLES.i = 0; CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer , "key" object, "value" object); WHILE( i < array_length(params)) BEGIN INSERT INTO "#__LOCAL__keyvalue_store" SELECT VARIABLES.i + 1 as "argument_number" , params[i+1][1] AS "key" , params[i+1][2] as "value" ; VARIABLES.i = VARIABLES.i + 1; END SELECT * from "#__LOCAL__keyvalue_store" ; END |
CREATE VIRTUAL PROCEDURE views.parse_params( IN params object, IN "key" string ) RETURNS ( "argument_number" integer , "key" string, "value" object ) AS BEGIN DECLARE integer VARIABLES.i = 0; CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer , "key" string, "value" object); WHILE( i < array_length(params)) BEGIN INSERT INTO "#__LOCAL__keyvalue_store" SELECT VARIABLES.i + 1 as "argument_number" , cast ( params[i+1][1] as string ) AS "key" , params[i+1][2] as "value" ; VARIABLES.i = VARIABLES.i + 1; END IF( "key" IS NOT NULL ) SELECT * from "#__LOCAL__keyvalue_store" WHERE "key" = "parse_params.key" ; ELSE SELECT * from "#__LOCAL__keyvalue_store" ; END ;; |
call views.parse_params( ARRAY(ARRAY( 'foo' , 'bar' ),ARRAY( 'baz' , 'boz' )));; -- with parameter call "alteryx.parse_params" ( "params" => ARRAY( ARRAY( 'bla' , 'asdasd' ),ARRAY( 'gggg' , 'ppppppppsd' ) ), "key" => 'gggg' );; |
Comments
0 comments
Please sign in to leave a comment.