CODEWITHSUNDEEP
jsonpostgresqlplpgsql
whitelined
whitelined

Reputation: 340

Queries involving data passed via JSONB

Ok, so I have a function that takes a JSONB type, and performs SELECT and INSERT based on various keys from that blob of data. PostgreSQL is throwing up errors regarding the PERFORM and INSERT. What's the correct usage of json data in SQL queries?

CREATE OR REPLACE FUNCTION add_revision(d jsonb)
RETURNS jsonb AS $$
DECLARE
    did INT;
BEGIN
    did:=get_drawing_id(d->>'Name');
    IF did=NULL THEN
        did:=create_drawing(d->>'Name',d->>'Discipline',
            d->>'Doc Type');
    END IF;
    PERFORM * FROM revisions WHERE drawingid=did AND Sequence=d->>'Sequence';
    IF NOT FOUND THEN
        INSERT INTO revisions (Sequence,Revision,State,Meta) VALUES(d->>'Sequence',
            d->>'Version',d->>'State',d);
        RETURN jsonb_build_object('ok',true);
    END IF;
    RETURN jsonb_build_object('ok',false,'message','Already exists');
END;
$$ LANGUAGE plpgsql;

Here is the error throw up

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42883]: Un
defined function: 7 ERROR:  operator does not exist: integer = text
LINE 1: ... * FROM revisions WHERE drawingid=did AND Sequence=(d->>'Seq...

Upvotes: 0

Views: 46

Answers (2)

whitelined
whitelined

Reputation: 340

It's perfectly fine to use JSONB functions in queries, I just had to typecast to the required type:

CREATE OR REPLACE FUNCTION add_drawing_revision(d jsonb)
RETURNS jsonb AS $$
DECLARE
    did INT;
BEGIN
    did:=get_drawing_id(d->>'Name');
    IF did<0 THEN
        did:=create_drawing(d->>'Name',d->>'Discipline',
            d->>'Doc Type');
    END IF;
    PERFORM * FROM revisions WHERE drawingid=did AND Sequence=(d->>'Sequence')::INT;
    IF NOT FOUND THEN
        INSERT INTO revisions (Sequence,Revision,State,Meta) VALUES((d->>'Sequence')::INT,
            d->>'Version',d->>'State',d);
        RETURN jsonb_build_object('ok',true);
    END IF;
    RETURN jsonb_build_object('ok',false,'message','Already exists');
END;
$$ LANGUAGE plpgsql;

Upvotes: 0

M&#225;rio Kapusta
M&#225;rio Kapusta

Reputation: 508

You cannot compare an integer with a text. PostgreSQL is strict.

Check for types in this section.

 drawingid=did AND Sequence=d->>'Sequence'

It shouldn't be a problem related to the JSONB.

Upvotes: 1

Related Questions