How to pass a text parameter to a stored function for an IN statement

Question

I need to get table names from a schema, except for some tables

CREATE OR REPLACE FUNCTION  func(unnecessary_tables TEXT)
returns void
as $$
begin
      EXECUTE 'SELECT table_name FROM information_schema.tables   
      WHERE 
      table_schema=''public''
      AND 
      table_name NOT IN( $1 )
      ' USING unnecessary_tables

      --here execute retrieved result, etc ...

end;
$$language plpgsql

Then the function is called

select func('table1'',''table2');

This does not work and returns the result table1and table2.

Question: how to pass a text parameter to a stored function for an operator IN?

Answer 1

Pass a text array instead of text:

create or replace function func(unnecessary_tables text[])
returns void as $$
begin
    select table_name
    from information_schema.tables   
    where
        table_schema = 'public'
        and
        not(table_name = any($1))
    ;
end;
$$language plpgsql    

Call it like this:

select func(array['t1','t2']::text[]);

By the way, the above code may be plain SQL instead of PL / pgSQL

Answer 2

To answer the exact question (how to go to the functional text for the operator IN) you need:

SELECT func( '''table1'',''table2''');

, , . , , :

  CREATE OR REPLACE FUNCTION  func(unnecessary_tables TEXT)
returns void
as $$
begin
      EXECUTE 'SELECT table_name FROM information_schema.tables   
      WHERE 
      table_schema=''public''
      AND 
      table_name NOT IN(' || unnecessary_tables || ')'; 

      --here execute retrieved result, etc ...

end;
$$language plpgsql

, USING "" $1.