Postgres Update Statement: how to set a column's value only if the column name contains a certa-CodePudding

Each of the following tables ['table1', 'table2'] are part of the public schema, knowing that each table may contain multiple columns containing the substring 'substring' in the name for Example let's look at the following :

Table_1 (xyz, xyz_substring,...some_other_columns... abc, abc_substring)
Table_2 (xyz, xyz_substring,..some_other_columns... abc, abc_substring)

I am coming to this from a pythonic way of thinking, but basically, how could one execute a statement without knowing exactly what to set since the columns we need to target need to meet a certain criteria ?

I had the idea to add a another loop over the column names of the current table and check if the name meets the criteria. then execute the query but It feels very far away from optimal.

DO $$
declare
    t text;
    tablenames  TEXT ARRAY  DEFAULT  ARRAY['table_1', 'table_2'];
BEGIN
    FOREACH t IN ARRAY tablenames
    LOOP
        raise notice 'table(%)', t;
        -- update : for all the column that contain 'substring' in their names set a value  
    END LOOP;
END$$;

EDIT: Thanks for the answer @Stefanov.sm , i followed exactly your thought and was able to base your logic to have just one statement :

DO $$
declare
    t text;
    tablenames  TEXT ARRAY  DEFAULT  ARRAY['table_1', 'table_2'];
    dynsql text;
    colname text;
BEGIN
    FOREACH t IN ARRAY tablenames LOOP
        raise notice 'table (%)', t;
        dynsql := format('update %I set', t);
        for colname in select column_name from information_schema.columns where table_schema = 'public' and table_name = t
            loop
                if colname like '%substring%' then
                    dynsql := concat(dynsql,format(' %I = ....whatever expression here (Make sure to check if you should use Literal formatter %L if needed) ....,',colname,...whateverargs...));
                end if;
            end loop; 
      dynsql := concat(dynsql,';'); -- not sure if required.
      raise notice 'SQL to execute (%)', dynsql;
      execute dynsql;
    END LOOP;
END;
$$;

CodePudding user response：

Extract the list of columns of each table and then format/execute dynamic SQL. Something like

DO $$
declare
    t text;
    tablenames text[] DEFAULT ARRAY['table_1', 'table_2'];
    dynsql text;
    colname text;
BEGIN
    FOREACH t IN ARRAY tablenames LOOP
        raise notice 'table (%)', t;
        for colname in select column_name 
          from information_schema.columns 
          where table_schema = 'public' and table_name = t loop
            if colname ~ '__substring$' then
                dynsql := format('update %I set %I = ...expression... ...other clauses if any...', t, colname);
                raise notice 'SQL to execute (%)', dynsql;
                execute dynsql;
            end if;
        end loop;        
    END LOOP;
END;
$$;

This will cause excessive bloat so do not forget to vacuum your tables. If your tables' schema is not public then edit the select from information_schema accordingly. You may use pg_catalog resource instead of information_schema too.