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.
