1

One of the SQL Queries use the same table name again and again -
Example -

select 
    rr.jdoc as child_node, json_agg(parent_rr.jdoc)::jsonb as parent_node ,   array_length(array_agg(parent_rr.jdoc)::jsonb[], 1) as count
    from MYTABLE  rr, MYTABLE  parent_rr
    where 
       parent_rr.jdoc @> (rr.jdoc->'somefield')::jsonb 
    group by rr.jdoc

UNION 

select rr.jdoc, NULL as parent_id, null as pcount
        from  MYTABLE rr where 
    not (rr.jdoc ?? 'somefiled') 
    and ((rr.jdoc->'crazyfiled'->>'doublecrazyfiled')<>'gotyou')

You can see the same MYTABLE is used 3 times. The thing is, I have to run the same query for different tables i.e the MYTABLE substituted.

So I am trying to find - if anything like below is possible -

SET TABLENAME=CUS_DELTA  --//then use the above like -
select * from $TABLENAME;

I am using postgres 13.5 with DBeaver for running the queries. If not SQL, does DBeaver provide any such means.

If SQL can not support it, I am ok to use Dynamic SQL / PL SQL (though i have not idea about these) - if its not going to be too complicate for the above case. But I am not aware about the code, so request to share the same through an answer.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
samshers
  • 1
  • 6
  • 37
  • 84
  • 1
    You need [Dynamic SQL](https://www.postgresql.org/docs/current/ecpg-dynamic.html), also [`EXECUTE ... USING` only works in PL/PgSQL](https://stackoverflow.com/a/12780331/724039) – Luuk Dec 30 '22 at 16:41
  • Dynamic SQL is your friend here. Beware of SQL Injection when concatenating string, and make sure the name of the table does not come from the UI or other external source. – The Impaler Dec 30 '22 at 16:42
  • 2
    In DBeaver you can create parameters with the `:` prefix (`SELECT ... FROM ... WHERE a = :foobar` ), this will promt a windows where you can bind these parameters to a certain value. – S.Visser Dec 30 '22 at 17:26

1 Answers1

1

Read this article about Dynamic SQL carefully. Example from the docs:

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "INSERT INTO test1 VALUES(?, ?);";
EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE mystmt FROM :stmt;
 ...
EXEC SQL EXECUTE mystmt USING 42, 'foobar';

Basically this way you can simply pass values. Now, your values are dynamic as well, but no worries. Declare your tablename as varchar and SELECT tbname INTO yourvariable FROM sometable. Then you can pass this variable to your dynamic query.

EDIT:

Minimalistic example:

Setting a variable to hard-coded value

SET TABLENAME = 'CUS_DELTA';

Setting a variable to a dynamic table name

SELECT tbname
INTO TABLENAME
FROM yourtable

And then let's use our TABLENAME variable like this:

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "SELECT * FROM ?";
EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE mystmt FROM :stmt;

EXEC SQL EXECUTE mystmt USING TABLENAME;

The above is untested.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • my project is bit hectic - would you mind adding the simplest code that works as-is for my case. sorry to bother.. – samshers Dec 30 '22 at 16:59
  • @samshers it will be interesting to take into account the schema you didn't share, but I will try :) – Lajos Arpad Dec 30 '22 at 17:17
  • @samshers I have provided an (untested) example, but you have not shared your schema, so I cannot be sure whether this fits into your schema. – Lajos Arpad Dec 30 '22 at 17:21