Select columns with the same prefix

Question

Using the following code I can select a few columns that share the same prefixes (either upreg_srt or downreg_srt) from my table and delete (drop) them:

DO
$do$
DECLARE
    _column TEXT;
BEGIN
FOR _column  IN
    SELECT DISTINCT quote_ident(column_name)
    FROM   information_schema.columns
    WHERE  table_name = 'all_se_13patients_downreg_ranks'
    AND    column_name LIKE '%upreg_srt' OR column_name LIKE '%downreg_srt'  
    AND    table_schema NOT LIKE 'pg_%'
    order by quote_ident
LOOP
    RAISE NOTICE '%',
  --  EXECUTE
  'ALTER TABLE all_se_13patients_downreg_ranks DROP COLUMN ' || _column;
END LOOP;
END
$do$

This code works nicely under Postgres. (Demark the --EXECUTE line first of course!) Is there a way to utilize/alter this code (or to use different scripting) in order to actually save the chosen columns (the ones with shared prefixes) into a daughter table? Pseudo-code:

select [my chosen columns]
into myNewTbl
from myOriginalTbl

I was able to run the following code:

DO
$do$
DECLARE 
qry  TEXT;
BEGIN
  SELECT 'SELECT id_13,' || substr(cols,2,length(cols)-2) ||
       ' FROM all_se_13patients_downreg_ranks' INTO qry
     FROM (
        SELECT array(
            SELECT DISTINCT quote_ident(column_name::text)
           FROM   information_schema.columns
           WHERE  table_name = 'all_se_13patients_downreg_ranks'
           AND    column_name LIKE '%downreg_srt' 
           order by quote_ident             
      )::text cols 
        -- CAST text so we can just strip off {}s and have column list
     ) sub;
     --EXECUTE qry;
     RAISE NOTICE '%',qry;
END 
$do$

It works nicely - but I can't use the EXECUTE qry line for some reason. If I try the RAISE NOTICE '%',qry; line I get an output - which is basically the command line that I can later copy/paste and execute it just fine in a new query window(!). Therefore, I'm wondering why the EXECUTE part doesn't work?

Running the procedure with the RAISE NOTICE line I get:

NOTICE: SELECT id_13,agk_downreg_srt,bvi_downreg_srt,cbk_downreg_srt,dj_downreg_srt,dkj_downreg_srt,flv_downreg_srt,ghw_downreg_srt,gvz_downreg_srt,idy_downreg_srt,prw_downreg_srt,spn_downreg_srt,zgr_downreg_srt,znk_downreg_srt FROM all_se_13patients_downreg_ranks

However, if I try to run the procedure with the EXECUTE part instead I get:

Query returned successfully with no result in 51 ms.

So the problem is that postgres fails to actually execute the command line. The question is WHY? And is there a better way to perform this procedure so it actually executes?

Answer 1

However, if I try to run the procedure with the EXECUTE part instead I get: "Query returned successfully with no result in 51 ms." - so the problem is that postgres fails to actually execute the command line

No, PostgreSQL successfully executed the query. That's what "Query returned successfully" means. It returned no result, and it took 51 ms.

If you want to execute a dynamic SELECT statement, and you want to see some kind of result, use execute ... into.

do
$$
declare
  qry  text;
  table_name text;
begin
  qry := 'select table_name from information_schema.tables where table_name like ''pg_%'';';
  raise notice '%', qry;
  execute qry into table_name;
  raise notice '%', table_name;
END 
$$

NOTICE:  select table_name from information_schema.tables where table_name like 'pg_%';
NOTICE:  pg_statistic
Query returned successfully with no result in 24 ms.

The value "pg_statistic" was the first row in the result set. Using execute this way assigns the value of only the first row to table_name. This is by design.

If you want to insert the column names into a table, you need to write an INSERT statement, not a SELECT statement.