By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create or replace function
fn_gen_tab_text ( curr_tab_in text,tab_text_in TEXT, new_tab_in text)
RETURNS void AS
$body$
declare v_sql TEXT;
BEGIN
select 'CREATE TABLE %I AS select ' || string_agg(column_name,',')
||' from %I where false'
into v_sql from information_schema.columns
where table_name = curr_tab_in
and column_name like '%'||tab_text_in||'%';
EXECUTE format (v_sql,new_tab_in,curr_tab_in);
END $body$ language plpgsql
create table host_table ( host_id int, host_url text, col1 text, col2 text,
average_host_rating int);
select fn_gen_tab_text('host_table','host','new_table' );
| fn_gen_tab_text |
|---|
select * from new_table;