How to use passed schema name dynamically in a function?

Question

I have function called list_customers, taking i_entity_id, i_finyear as input params. The schema name is built from i_finyear, I need to execute the query based on the given schema.

I tried the below code:

CREATE OR REPLACE FUNCTION list_customers(i_entity_id integer,
i_finyear integer) 
RETURNS TABLE(entity_id integer, client_id
integer, financial_yr integer) LANGUAGE 'plpgsql' AS 
$BODY$
declare finyear integer := i_finyear; 
    schema_1 text := 'tds'||''||i_finyear;
begin 
    set search_path to schema_1;
return query select
d.entity_id, d.client_id, d.financial_yr
from schema_1.deductor d where d.entity_id = 1331;
end; 
$BODY$;

Then:

select tds2020.list_customers(1331,2022);
   

imagelink

Answer 1

You need dynamic SQL with EXECUTE:

CREATE OR REPLACE FUNCTION list_customers(i_entity_id int, i_finyear int)
  RETURNS TABLE (entity_id int, client_id int, financial_yr int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
     'SELECT d.entity_id, d.client_id, d.financial_yr
      FROM   tds' || i_finyear || '.deductor d
      WHERE  d.entity_id = $1'
   USING i_entity_id;
END
$func$;

Since the input parameter i_finyear is type integer, there is no danger of SQL injection and you can use plain concatenation to concatenate your schema name like "tbl2016". Else, you'd use format() to defend against that. See:

• Table name as a PostgreSQL function parameter

You can also concatenate (properly quoted) values, but it's safer and more efficient to pass the value with the USING keyword. See:

• Replace double quotes with single quotes in Postgres (plpgsql)

No need to change the search_path additionally. That would just add an expensive context switch.