Get type of current column inside loop-CodePudding

I'm trying to do a function that set all varchar and text columns to uppercase

CREATE OR REPLACE FUNCTION insert_to_uppercase() RETURNS trigger AS $insert_to_uppercase$
    DECLARE wtable_name varchar(50);
            wcolumn_name varchar(50);
            wcolumn_type varchar(50);
            query text; -- TODO: remove later
    BEGIN   
        wtable_name := pg_typeof(NEW);
        FOR wcolumn_name IN 
            SELECT column_name 
              FROM information_schema.Columns
              WHERE table_name = wtable_name 
        
        LOOP
          query := format('select pg_typeof(%s) from %s', wcolumn_name, wtable_name);
          RAISE NOTICE 'query: %', query;
          EXECUTE query INTO wcolumn_type;
          RAISE NOTICE 'column type: %' , wcolumn_type;
          
          -- TODO: if wcolumn_type is varchar/text then uppercase
        END LOOP;
        RETURN NEW;
    END;
$insert_to_uppercase$ LANGUAGE plpgsql;

As you can see, the idea is: loop through a table, saving it's name and the name of the current column into wtable_name and wcolumn_name variables, respectively. Then, I'm trying to take the value from the "select value_of[...]" to store into another variable. For some reason, that execute is not working, the value of wcolumn_type is always null.

Edit1: There's a trigger that call this function before an insert statement. Here's the output from raise notice:

NOTICE:  query: select pg_typeof(cod_modelo_refil) from pfc_modelos_refil
NOTICE:  column type: <NULL>
NOTICE:  query: select pg_typeof(nome_modelo) from pfc_modelos_refil
NOTICE:  column type: <NULL>
NOTICE:  query: select pg_typeof(preco) from pfc_modelos_refil
NOTICE:  column type: <NULL>
NOTICE:  query: select pg_typeof(descricao) from pfc_modelos_refil
NOTICE:  column type: <NULL>

and the description from the table i'm using

      Column      |         Type          | Collation | Nullable | Default 
------------------ ----------------------- ----------- ---------- ---------
 cod_modelo_refil | integer               |           | not null | 
 nome_modelo      | character varying(50) |           | not null | 
 preco            | numeric(18,2)         |           |          | 
 descricao        | text                  |           |          |

CodePudding user response：

Probably the table is empty, so the query returns no result. You can count yourself lucky; the table could also have contained billions of rows...

To get the data type of a column, you should query information_schema.columns.