How to execute SQL code with concatenated table name?

Question

An example of what I am attempting to do:

SELECT  count(*) as "count" 
FROM (
   SELECT overlay('db_xx.company_summary' placing 'US' from 4)
) as s

This returns count to be 1, while there are many rows in the table db_us.company_summary.
I would expect it to count all rows, similar to this query:

SELECT count(*) as "count" 
FROM   db_us.company_summary

I attempted the overlay function, to be similar to the above query. I am not sure if its possible to do this in SQL.

In Python you would do something like this:

"hello {}".format("world")

So I would like the input string to act as a SQL syntax command.

I removed the inconsistent database tags. Please tag only with the database you are really using. — Gordon Linoff, Aug 19 '20 at 20:11
Normally your RDBMS might support dynamic SQL, using which you can execute a query from a string (so you build the query string with whatever substitutions you would like and pass it to execute) — Alexey S. Larionov, Aug 19 '20 at 20:48
I understand this, but I just don't know how. Am I asking a question that is not possible to solve using a simple SQL function? I'm not sure. — Asael, Aug 19 '20 at 20:50
`select overlay('db_xx.company_summary' ..)` returns a single row with a single string value. Why do you expect that to return anything else? https://dbfiddle.uk/?rdbms=postgres_12&fiddle=807a20a72b3d20323d90ed1cff710376 — , Aug 19 '20 at 20:53
I was just painting a picture what I wanted to do. Obviously it doesn't translate. — Asael, Aug 19 '20 at 20:54
You can use an approach like [this](https://stackoverflow.com/a/38684225/330315) if you need to do the count with dynamic SQL. — , Aug 19 '20 at 20:55
thanks @a_horse_with_no_name this looks promising. Will comeback. — Asael, Aug 19 '20 at 20:58

Erwin Brandstetter · Answer 1 · 2023-02-27T23:03:54.953

Plain SQL does not allow to parameterize identifiers (or anything but values). You need dynamic SQL. Examples:

Example PL/pgSQL function with dynamic SQL:

CREATE OR REPLACE FUNCTION f_counts()
  RETURNS SETOF bigint
  LANGUAGE plpgsql AS
$func$
DECLARE
   _lower_iso2 text;
   _ct bigint;
BEGIN
   FOR _lower_iso2 IN
      SELECT lower(iso2) FROM country_code
   LOOP
      RETURN QUERY EXECUTE
      format ('SELECT count(*) AS count FROM %I.company_summary'
            , overlay('db_xx' PLACING _lower_iso2 FROM 4)
             );
   END LOOP;
END
$func$;

Call:

SELECT * FROM f_counts();

Result:

f_counts
---------
2
3
4

db<>fiddle here

Be aware that Postgres identifiers are case sensitive:

Are PostgreSQL column names case-sensitive?

How to execute SQL code with concatenated table name?

1 Answers1