PostgreSQL column "exists" does not exist-CodePudding

I have this query for a function in postgresql

EXECUTE FORMAT('SELECT * FROM riesgo.%I WHERE codigo = %L', TG_TABLE_NAME, NEW.codigo) INTO _colvar;
IF EXISTS _colvar THEN
    UPDATE riesgo.ro_agresion SET
        (nombre,creado_por,valor,activo) = 
        (NEW.nombre,NEW.creado_por,NEW.valor,NEW.activo)
    WHERE codigo = NEW.codigo;
ELSE
    RETURN NEW;
END IF;
RETURN NULL;

and I'm getting this

ERROR:  column "exists" does not exist
LINE 1: SELECT EXISTS _colvar

CodePudding user response：

The explanation for the confusing error can be found in the manual for pl/pgsql Expressions, which explains:

When you write a PL/pgSQL statement IF expression THEN ... PL/pgSQL will evaluate the expression by feeding a query like SELECT expression to the main SQL engine

So in your case, the expression is being translated to SELECT EXISTS _colvar, which looks to the query parser like a column named "EXISTS" being given an alias _colvar".

To fix it, you need something that would be valid in a select list. For instance:

IF _colvar IS NOT NULL ...