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

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?

CodePudding user response：

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: