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
– Gordon Linoff, Commented Aug 19, 2020 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
– Alexey S. Larionov, Commented Aug 19, 2020 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
– Asael, Commented Aug 19, 2020 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? dbfiddle.uk/… — user330315
– user330315, Commented Aug 19, 2020 at 20:53
I was just painting a picture what I wanted to do. Obviously it doesn't translate. — Asael
– Asael, Commented Aug 19, 2020 at 20:54

Erwin Brandstetter · Accepted Answer · 2023-02-27 23:03:54Z

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?

Collectives™ on Stack Overflow

How to execute SQL code with concatenated table name?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related