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 multiplegroup_id
s 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
anditem_id
)(only for the inputgroup_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
Here's how I achieved UPSERT with DELETE missing rows, if anyone is looking to do the same.
This function removes rows that are missing from your
in_rows
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.main function:
call it:
references: