How to pass text parameter to stored function for `IN` operator

Question

I need obtain table names from schema, except 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 call function

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

This not works and returns in result table1 and table2 also.

Question is: How to pass text parameter to stored function, for IN operator ?

Ran

Shouldn't it be select func('table1, table2');? – Lonna

@Gabriel's No, I am trying this also, but not works – Ran

Have you tried SELECT func( '''table1'',''table2''');? I think there was a problem. – Lonna

Answer 1

Pass a text array in 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:

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

BTW the code above can be plain SQL in instead of PL/pgSQL

Dray

It can be enhanced by VARIADIC parameter - then you can call the function by SELECT func('t1','t2') – Honeywell

Answer 2

To answer you exact question (How to pass to function text for IN operator) You need:

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

The reason is that table names must by string, so they need to by inside quotes. To make it works there is one change in code needed which I did't see at first:

  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

It's needed because USINGis aware of types and don't just "paste" parameter in place of $1.

Borscht

This works for you? Iam trying this also, but it was not working – Ran

Oh, I did't notice you use EXECUTE ... USING .... I've edited my answer. – Lonna

Answer 3

I don't think none of above answers are correct.

select pg_typeof(table_name),table_name::text 
   from information_schema.tables   
   where table_schema = 'public';

It will return:

             pg_typeof             |        table_name
-----------------------------------+--------------------------
 information_schema.sql_identifier | parent_tree

from Which means at least the table_name should be cast to text. Here is my solution:

create or replace function n_fnd_tbl(_other_tables text[])
returns table(__table_name text) as 
$$
begin
  return query EXECUTE format('
    select table_name::text
    from information_schema.tables   
    where table_schema = ''public''
    and table_name  <> ''%s''',_other_tables );
end
$$language plpgsql;

Then call it:

select * from n_fnd_tbl(array['tableb','tablea']::text[]);

Dykstra