Wildcard search using parameters in function with dynamic SQL
Let's suppose for country_name you wanted to do a leading and trailing wildcard search.


You don't need dynamic SQL for this. Just:

CREATE OR REPLACE FUNCTION report_get_countries_new (_pattern text)
  RETURNS SETOF lookups.countries AS
$func$
   SELECT *
   FROM   lookups.countries
   WHERE  country_name LIKE '%' || _pattern || '%'
$func$ LANGUAGE sql;


Call:

SELECT * FROM report_get_countries_new ('ic');  -- without wildcards!


This negates SQL injection risk completely, since that comes with dynamic SQL.

The caller can still include wildcards at will (unless you process the parameter to filter wildcards), but there is a hardcoded leading wildcard and also a trailing one (unless the parameter ends with \ removing the special meaning from the trailing %).

Even if you work with dynamic SQL and EXECUTE in PL/pgSQL, there is no risk for SQL injection as long as you pass values as values with the USING clause:

Related:


SQL injection in Postgres functions vs prepared queries
PostgreSQL Stored Procedure Performance
Table name as a PostgreSQL function parameter