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.

Vao Tsun · Accepted Answer · 2018-01-08 13:05:59Z

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)

edited Jan 8, 2018 at 13:05

answered Jan 8, 2018 at 12:51

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gustavo Over a year ago

What '%I' stands for?

Gustavo Over a year ago

The error has changed, now its: Query failed: ERROR: null values cannot be formatted as an SQL identifier CONTEXT... I guess some value there is null, but I don't know why

Vao Tsun Over a year ago

it's a different question - you should ask it as a new post. but in short - add coalesce to every of three columns you select in cur_atri and error will tell you where you had null. or just add where (atri_nome, atri_tipo, tamanho) is not null as if one column of row is null the row itself is null

flaviodesousa · Accepted Answer · 2018-01-08 13:12:02Z

0

You have a dangling '||' on

elsif tipo = 1 then query := 'alter table '||colecao_nome||' add column '||nome||' character varying '||tamanho||;

so change it to

elsif tipo = 1 then query := 'alter table '||colecao_nome||' add column '||nome||' character varying '||tamanho;

answered Jan 8, 2018 at 13:12

flaviodesousa

7,9954 gold badges30 silver badges35 bronze badges

Collectives™ on Stack Overflow

Operator does not exist Postgres

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related