Using a function to take a parameter and test it against a table and update based on cirteria in Pos-CodePudding

I'm trying to write a function that will take some tempData object as a parameter in Postgres and check to see if the item exists within the table. If it does then update the "state" field from false to true. If the item does exist then insert a new item with all of the fields into the db.

tempData = {
"bsa": "PPCM",
"id": "b18bde5d-aeb4-492d-b0de-c45437da94ac",
"state": true
}

So I believe I need to use EXISTS:

IF EXISTS (SELECT id FROM table_name t WHERE t.id = 'b18bde5d-aeb4-492d-b0de-c45437da94ac') THEN
  UPDATE table_name
  SET state = true;
ELSE
  INSERT INTO table_name (bsa, id, state)
  VALUES (PPCM, 'b18bde5d-aeb4-492d-b0de-c45437da94ac', true);
END IF;

How do I pass the parameters into the sql query using a function to pass those parameters?

CodePudding user response：

If id is a primary key (or has a unique restriction) you can do this operation (upsert) on Postgres with:

INSERT INTO table_name(bsa, id, state)
  VALUES ('PPCM', 'b18bde5d-aeb4-492d-b0de-c45437da94ac', true)
ON CONFLICT (id) 
DO 
   UPDATE SET state = true;

CodePudding user response：

CREATE temp TABLE table_name (
    state boolean,
    id uuid PRIMARY KEY,
    bsa text
);

INSERT INTO table_name (state, id, bsa)
    VALUES (FALSE, 'b18bde5d-aeb4-492d-b0de-c45437da94ac', 'PPCM');

--main function:

CREATE OR REPLACE FUNCTION misc (IN tempdata jsonb)
    RETURNS void
    AS $$
DECLARE
    _id uuid;
    _state boolean;
    _bsa text;
BEGIN
    RAISE NOTICE 'jsonb.state: %', tempdata['state']::text;
    RAISE NOTICE 'jsonb.id: %', tempdata['id']::text;
    RAISE NOTICE 'jsonb.bsa: %', tempdata['bsa']::text;
    _id := (tempdata ->> 'id')::text::uuid;
    _state := tempdata['state']::text::boolean;
    _bsa := tempdata ->> 'bsa'::text;
    INSERT INTO table_name (state, id, bsa)
        VALUES (_state, _id, _bsa)
    ON CONFLICT (id)
        DO UPDATE SET
            state = excluded.state;
END
$$
LANGUAGE plpgsql;

test it:

  SELECT
    *
FROM
    misc ('{"bsa": "PCM","state": true,"id": "b18bde5d-aeb4-492d-b0de-c45437da94ac"}');