Use column as schema name in SQL statement

Question

I know this is a bit of a far-fetched question, but is it in any way possible to do following?

select cc.schema,
      (select exists(select * 
                     from (select cc.schema).helpers cs
                     where cs.chain_id=cc.id)
      ) as hasHelpers
from customer.chain cc 
where cc.id=$1

What I'm trying to do is convert cc.schema to the schema name in the exists statement! Is this possible and how?

If I try to run the statement above I get an error:

subquery in FROM must have an alias .... from (select cc...

one way is to write a function which will check if shema exists, then if exists with an execute 'select * from ''||$1||''.helpers cs where cs.chain_id=''$2'')' into ... checks if value is present — LongBeard_Boldy, Dec 08 '15 at 11:53

score 0 · Answer 1 · edited May 23 '17 at 11:52

You need dynamic SQL for this. I suggest a function:

CREATE OR REPLACE FUNCTION f_chk_schemas(_id integer)
  RETURNS TABLE (schema text, schema_exists bool, has_helpers bool) AS
$func$
BEGIN
   FOR schema, schema_exists, has_helpers IN
      SELECT cc.schema, to_regclass(quote_ident(cc.schema) || '.helpers') IS NOT NULL
      FROM   customer.chain cc
      WHERE  cc.id = $1
   LOOP
      IF schema_exists THEN 
         EXECUTE format('SELECT EXISTS (SELECT 1 FROM %I.helpers WHERE id = $1)', schema)
         USING $1
         INTO  has_helpers;
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Use column as schema name in SQL statement

1 Answers1