Operator does not exist Postgres

Question

I've created a trigger function to dynamically create tables, here it is:

declare 
   tipo smallint;
   tamanho smallint;
   nome character varying(100);
   cur_atri cursor for select atri_nome, atri_tipo, tamanho from atributo where colecao = new.col_id;
   colecao_nome text := 'marcacao_'||new.col_nome;
   query text;
begin

if new.col_aprovada is true and old.col_aprovada is false then
open cur_atri;

   query := 'create table '||colecao_nome||' (id serial not null, colecao integer not null references public.colecao (col_id), primary key (id))';
   execute query;

   loop
      fetch cur_atri into nome, tipo, tamanho;
      exit when not found;

      if tipo = 0 then query := 'alter table '||colecao_nome||' add column '||nome||' integer';
      elsif tipo = 1 then query := 'alter table '||colecao_nome||' add column '||nome||' character varying '||tamanho||;
      elsif tipo = 2 then query := 'alter table '||colecao_nome||' add column '||nome||' boolean';
      elsif tipo = 3 then query := 'alter table '||colecao_nome||' add column '||nome||' real';
      elsif tipo = 4 then query := 'alter table '||colecao_nome||' add column '||nome||' bytea';
      end if;

      execute query;
    end loop;
    close cur_atri;
end if;
end;

This error is shown on my website when the trigger is activated:

Message: pg_query(): Query failed: ERROR: operator does not exist: text || LINE 1: ...me||' add column '|| nome ||' character varying '||tamanho|| ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT 'alter table '||colecao_nome||' add column '|| nome ||' character varying '||tamanho|| CONTEXT: PL/pgSQL function criar_tabela_colecao() line 21 at assignment

I really don't know what is causing it.

Answer 1

Instead of such concatenation, try using format. smth like:

if tipo = 0 then query := format('alter table %I add column %I integer',colecao_nome,nome);

https://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-FORMAT

I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).

(formatting mine)