Convert MySQL Variable Query to PostgreSQL-CodePudding

I have migrated MYSQL database to PostgreSQL and I am now converting my queries to match. I have this MySQL query in the Python file:

SET @Drop_Stm = CONCAT('DROP TABLE ', (SELECT GROUP_CONCAT(TABLE_NAME) AS All_Tables 
FROM information_schema.tables 
WHERE TABLE_NAME LIKE 'temp_%' AND TABLE_SCHEMA = '{client_name}'))

I would like to make it works in Postgres, I tried the following but returns error:

WITH Drop_Stm AS (CONCAT('DROP TABLE ', (SELECT STRING_AGG(TABLE_NAME, ',') AS All_Tables 
FROM information_schema.tables 
WHERE TABLE_NAME LIKE 'temp_%' AND TABLE_SCHEMA = '{client_name}')))

LINE 1: WITH Drop_Stm AS (CONCAT('DROP TABLE ', (SELECT STRING_AGG(T...
                      ^

I also tried DECLARE, SET, and DO $$ .. END $$ with no luck

CodePudding user response：

The query itself should be changed to this:

select concat('drop table ', string_agg(format('%I.%I', table_schema, table_name), ','), ' cascade;')
FROM information_schema.tables 
WHERE TABLE_NAME LIKE 'temp_%' 
  AND TABLE_SCHEMA = '{client_name}'

Note that I used the format() function to properly deal with identifiers needing quoting. I also generated a fully qualified table name (including the schema) so that you don't accidentally drop a table in the current schema, rather than {client_name}.

If you want to run the generated script, I see two options. One is to put this into a PL/pgSQL block and use execute:

do
$$
declare
  l_stmt text;
begin
  select concat('drop table ', string_agg(format('%I.%I', table_schema, table_name), ','), ' cascade;')
    into l_stmt
  FROM information_schema.tables 
  WHERE TABLE_NAME LIKE 'temp_%' 
    AND TABLE_SCHEMA = '{client_name}';
  
  execute l_stmt;
end;
$$
;

I don't know Python, so I am not entirely sure if {client_name} gets replaced correctly with that approach.

Another option is to run the SELECT query in Python, and store the result into a Python variable then run that SQL through Python.