Postgres : Dynamic SQL , Dynamic Parameters , Named Parameters !

 I was searching for a way to execute dynamic SQL in a Postgres function and that was straight forward : 

Execute ... .Using

but there is no way of passing dynamic parameters to Using that I know of and whenever I tried to execute something like :

Execute 'Execute ''...My sql Query with parameters $1 $2 $3...'' Using V1,V1,V3'  

I got unexpected error.  

Eventually one of my colleagues pointed out that you can pass an array data as parameter to Execute and use parameters such as $1[1] .  This was awesome and I took that one step further and build SQL based on json parameters to benefit from named parameters.  Here is an example: 

CREATE OR REPLACE FUNCTION aid_card.testfunction()

RETURNS integer

LANGUAGE plpgsql

AS $function$

    DECLARE

         _jsonParams json := '{"amount":0,"paymentStatus":1, "issueDate":"2022-01-23"}';

       _querySrc text := 'Select count(*) from "Payment" ap where "IssueDate" > to_date($issueDate,''YYYY-MM-DD'') and  "Amount" > cast ($amount as numeric)  and "PaymentStatus_Id" = $paymentStatus::integer' ;     

       _query text ;

       _result integer = 0;

    BEGIN

         _query := REGEXP_REPLACE(_querySrc, '(\W)\$(\w+)(\W?)', '\1($1->>''\2'')\3','g');

          -- RAISE NOTICE '%', _query;

         EXECUTE  _query into _result USING  _jsonParams;

         -- RAISE NOTICE '%', _result;

         return _result;

    END;

$function$

So basically the query can use named parameters but they have to be cast in some way to the expected data type.  The regex replace on the query transforms paramters to $1->>jsonpropertyname.  which extracts the json parameter name from the json params object.