skip to Main Content

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?

2

Answers


  1. Chosen as BEST ANSWER

    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. 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