Question 90481 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

  1. takes set of row values for a single group_id, fail if multiple group_ids present in in input rows
  2. compares it with matching values in the table (only for that group_id
  3. updates changed values (only for the input group_id)
  4. inserts new values
  5. 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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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


  2. 0

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search