Select columns with the same prefix
Using the following code, I can select multiple columns that use the same prefixes (or upreg_srt or downreg_srt) from my table and remove (delete) 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 well under Postgres. (Demark the --EXECUTE
line, of course, of course!) Is there a way to use / modify this code (or use different scripts) to actually store the selected columns (the ones with common prefixes) into a child 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$
This works well, but for some reason I cannot use the string EXECUTE qry
. If I try the line RAISE NOTICE '%',qry;
, I get output, which is basically a command line, which I can later copy / paste and execute as best I can in a new query window (!). So I'm wondering why the part is EXECUTE
n't working?
By executing the procedure using a string RAISE NOTICE
, 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 a procedure with a part EXECUTE
, I get:
The request returned successfully with no result in 51ms.
So the problem is that postgres is not actually executing the command line. Question: WHY? And is there a better way to accomplish this procedure so that it actually gets done?
source to share
However, if I try to execute the procedure using EXECUTE, I get instead: "The request returned successfully, with no 51ms result." - So the problem is that postgres does not actually execute the command line
No, PostgreSQL completed the query successfully. This means that "the request returned successfully". It got no result and it took 51ms.
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 pg_statistic value was the first row in the result set. Using execute
this path only assigns a value to the first line table_name
. This is by design.
If you want to insert column names into a table, you need to write an INSERT statement, not a SELECT statement.
source to share