Postgresql function (upsert and delete): how to pass a set of rows of table type to function call

Question

I have a table

CREATE TABLE items(
   id SERIAL PRIMARY KEY,
   group_id INT NOT NULL,
   item_id INT NOT NULL,
   name TEXT,
   .....
   .....
);

I am creating a function that

takes set of row values for a single group_id, fail if multiple group_ids present in in input rows
compares it with matching values in the table (only for that group_id
updates changed values (only for the input group_id)
inserts new values
deletes table rows that are absent in the row input (compare rows with group_id and item_id)(only for the input group_id)

this is my function definition

CREATE OR REPLACE FUNCTION update_items(rows_input items[]) RETURNS boolean as $$
DECLARE
  rows items[];
  group_id_input integer;
BEGIN
  -- get single group_id from input rows, fail if multiple group_id's present in input
  -- read items of that group_id in table
  -- compare input rows and table rows (of the same group_id)
  -- create transaction
    -- delete absent rows
    -- upsert
  -- return success of transaction (boolean)
END;
$$ LANGUAGE plpgsql;

I am trying to call the function in a query

select update_items(
(38,1,1283,"Name1"),
(39,1,1471,"Name2"),
(40,1,1333,"Name3")
);

I get the following error Failed to run sql query: column "Name1" does not exist

I tried removing the id column values: that gives me the same error

What is the correct way to pass row values to a function that accepts table type array as arguments?

Data should be in single quotes not double try using 'Name1' — Pavan Kumar T S, Dec 19 '22 at 06:16
@PavanKumarTS tried that, now I am getting a different error `Failed to run sql query: function update_items(record, record, record) does not exist` — Vikramark, Dec 19 '22 at 06:21
You cannot pass array of data as multiple parameters. refer this https://stackoverflow.com/a/2885769/7887883 — Pavan Kumar T S, Dec 19 '22 at 06:30

score 1 · Accepted Answer · answered Dec 19 '22 at 08:37

updates changed values
inserts new values deletes table rows that are
absent in the row input (compare rows with group_id and item_id)

If you want do upsert, you must upsert with unique constraint. So there is two unique constraints. primary key(id), (group_id, item_id). insert on conflict need consider these two unique constraint.

Since You want pass items[] type to the functions. So it also means that any id that is not in the input function arguments will also be deleted.

drop table if exists items cascade;
begin;
CREATE TABLE items(
   id bigint GENERATED BY DEFAULT as identity PRIMARY KEY,
   group_id INT NOT NULL,
   item_id INT NOT NULL,
   name TEXT
   ,unique(group_id,item_id)
);
insert into items values 
(38,1,1283,'original_38'),
(39,1,1471,'original_39'),
(40,1,1333,'original_40'),
(42,1,1332,'original_42');
end;

main function:

CREATE OR REPLACE FUNCTION update_items (in_items items[])
    RETURNS boolean
    AS $FUNC$
DECLARE
    iter items;
    saved_ids bigint[];
BEGIN
    saved_ids := (SELECT ARRAY (SELECT (unnest(in_items)).id));
    DELETE FROM items
    WHERE NOT (id = ANY (saved_ids));
    FOREACH iter IN ARRAY in_items LOOP
        INSERT INTO items
        SELECT
            iter.*
        ON CONFLICT (id)
            DO NOTHING;
        INSERT INTO items
        SELECT
            iter.*
        ON CONFLICT (group_id,
            item_id)
            DO UPDATE SET
                name = EXCLUDED.name;
        RAISE NOTICE 'rec.groupid: %, rec.items_id:%', iter.group_id, iter.item_id;
    END LOOP;
    RETURN TRUE;
END
$FUNC$
LANGUAGE plpgsql;

call it:

SELECT
    *
FROM
    update_items ('{"(38, 1, 1283, Name1) "," (39, 1, 1471, Name2) "," (40, 1, 1333, Name3)"}'::items[]);

references:

Thank you for the response. This code will delete all the unmatched rows from the table, however I want to delete unmatched rows of only that specific ```group_id```. So I want the input rows to only have a single ```group_id``` and the function should leave the other ```group_id``` rows unchanged. I'll edit the question to better reflect this. — Vikramark, Dec 19 '22 at 10:36
you can use https://dbfiddle.uk/btGcOH30, or tell me which row should be saved. which row should be deleted..@Vikramark — jian, Dec 19 '22 at 10:38
- input rows should have only one distinct `group_id`. the function should fail, if more than one `group_id`s are present in the input rows. - function should upsert only for that group id, and delete the table rows of that unique `group_id` (from the input rows) which are absent from the input. — Vikramark, Dec 19 '22 at 10:41
@Vikramark that can be done. unnest the input argument then count the distinct group_id, check count is > 1 or not. Please share demo use dbfiddle or edit the question to describe the expected result. — jian, Dec 19 '22 at 10:44
I am using an insert query with an on conflict clause, the problem is that my `in_items` array doesn't have `id` values as `NULL`. I get the error `null value in column id voilates not-null constraint` — Vikramark, Dec 20 '22 at 17:17
I have achieved what I was trying to achieve. Here's the code if you'd like to see it. https://pastebin.com/FuHYg49j. I got rid of the table's serial `id` column as that was giving me a trouble with the upsert, and I didn't really need it. — Vikramark, Dec 22 '22 at 03:36

score 0 · Answer 2 · answered Dec 22 '22 at 03:40

Here's how I achieved UPSERT with DELETE missing rows, if anyone is looking to do the same.

CREATE OR REPLACE FUNCTION update_items(in_rows items[]) RETURNS INT AS $$
DECLARE
  in_groups INTEGER[];
  in_group_id INTEGER;
  in_item_ids INTEGER[];
BEGIN
  -- get single group id from input rows, fail if multiple group ids present in input
  in_groups = (SELECT ARRAY (SELECT distinct(group_id) FROM UNNEST(in_rows)));
  IF ARRAY_LENGTH(in_groups,1)>1 THEN
    RAISE EXCEPTION 'Multiple group_ids found in input items: %', in_groups;
  END IF;
  in_group_id = in_groups[1];
  -- delete items of this group that are absent in in_rows
  in_item_ids := (SELECT ARRAY (SELECT (UNNEST(in_rows)).item_id));
  DELETE FROM items
    WHERE 
      master_code <> ANY (in_item_ids)
      AND group_id = in_group_id;
  -- upsert in_rows
  INSERT INTO items 
    SELECT * FROM UNNEST(in_rows)
    ON CONFLICT (group_id,item_d)
      DO UPDATE SET
        parent_group_id = EXCLUDED.parent_group_id,
        mat_centre_id = EXCLUDED.mat_centre_id,
        NAME = EXCLUDED.NAME,
        opening_date = EXCLUDED.opening_date;
  RETURN in_group_id;
  -- return success of transaction (boolean)
END;
$$ LANGUAGE plpgsql;

This function removes rows that are missing from your in_rows

Postgresql function (upsert and delete): how to pass a set of rows of table type to function call

2 Answers2