CODEWITHSUNDEEP
jsonpostgresqlfunctiontriggers
Nik
Nik

Reputation: 421

ERROR: cannot cast type integer to json in Postgresql

When I am trying to insert the record in tableA, i am getting the below error:

QUERY:  insert into schemaname.tableB(col1, col2) values(....)
CONTEXT:  PL/pgSQL function schemaname.funcA() line 5 at SQL statement
********** Error **********
ERROR: cannot cast type integer to json
SQL state: 42846
Context: PL/pgSQL function schemaname.funcA() line 5 at SQL statement

I have created following trigger which function when record gets committed:

CREATE CONSTRAINT TRIGGER trg_name
  AFTER INSERT
  ON schemaname.tableA
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE PROCEDURE schemaname.funcA();

In the below trigger function col1 is of integer type and col2 is of json type.

CREATE OR REPLACE FUNCTION schemaname.funcA()
  RETURNS trigger AS
$BODY$

BEGIN

insert into schemaname.tableB(col1, col2) values(NEW.val1, json_build_object("abc", NEW.col2val1::json, "def", NEW.col2val2::json, "ghi", NEW.col2val3::json));

RETURN NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Could you please help me what could be the cause of this error?

Upvotes: 2

Views: 8735

Answers (2)

Nik
Nik

Reputation: 421

CREATE OR REPLACE FUNCTION schemaname.funcA()   RETURNS trigger AS $BODY$

    BEGIN

    insert into schemaname.tableB(col1, col2) values(NEW.col1, CAST('{"nameA" : ' ||'"'||NEW.nameA||'", ' || '"nameB" : '||'"'||NEW.nameB||'", ' ||'"nameC" : '||'"'||NEW.nameC||'", ' || '"nameD" : '||'"'||NEW.nameD||'"}' as json));

    RETURN NEW;

    END; $BODY$   LANGUAGE plpgsql VOLATILE   COST 100;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246578

There is no cast from integer to json.

But since json_build_object accepts arguments of any type, you can solve the problem by removing the ::json casts in your trigger function.

By the way, using "abc" as a string constant is wrong – probably an error introduced while trying to obfuscate your code. You have to write '"abc"'.

Upvotes: 1

Related Questions