how to convert string as sql syntax

Question

An example 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, I would expect it to count all rows in db_us.company_summary 1*10^6

I would expect this to result in a query to similar to this;

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.

Normally in python you would do something like this;

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

So I would like the inputted 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 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 — a_horse_with_no_name, 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. — a_horse_with_no_name, 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 · 2020-08-27T22:03:55.230

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

Something like this:

Minimal test setup (should be in the question):

CREATE TABLE country_code (iso2 text PRIMARY KEY);
INSERT INTO country_code VALUES ('US'), ('GB'), ('AT');
CREATE SCHEMA db_us;
CREATE SCHEMA db_gb;
CREATE SCHEMA db_at;
CREATE TABLE db_us.company_summary (foo int);
CREATE TABLE db_gb.company_summary (foo int);
CREATE TABLE db_at.company_summary (foo int);
INSERT INTO db_us.company_summary VALUES (1), (2);
INSERT INTO db_gb.company_summary VALUES (1), (2), (3);
INSERT INTO db_at.company_summary VALUES (1), (2), (3), (4);

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. See:

Are PostgreSQL column names case-sensitive?

how to convert string as sql syntax

1 Answers1