From here
https://wordpress.com/post/michaelellerbeck.com/3883
Basically, riffing of Tim Dietrich Tulip: DataTable Custom Widget
Let’s build some test data
CREATE TABLE public.vehicles ( vehicle_id serial4 NOT NULL, make varchar(50) NULL, model varchar(50) NULL, "year" int4 NULL, color varchar(30) NULL, registration_number varchar(20) NULL, owner_id int4 NULL, is_active bool DEFAULT true NULL, CONSTRAINT vehicles_pkey PRIMARY KEY (vehicle_id), CONSTRAINT vehicles_registration_number_key UNIQUE (registration_number));INSERT INTO vehicles (make, model, year, color, registration_number, owner_id) VALUES('Toyota', 'Camry', 2022, 'Silver', 'ABC123', 1),('Honda', 'Civic', 2020, 'Black', 'XYZ789', 2),('Ford', 'Focus', 2018, 'Blue', 'FGH456', 3),('Tesla', 'Model 3', 2021, 'White', 'TES321', 4),('Chevrolet', 'Impala', 2019, 'Red', 'CHE123', 5),('BMW', '3 Series', 2022, 'Black', 'BMW456', 6),('Audi', 'A4', 2020, 'Grey', 'AUD789', 7),('Subaru', 'Outback', 2021, 'Green', 'SUB321', 8),('Nissan', 'Altima', 2019, 'Blue', 'NIS123', 9),('Kia', 'Sorento', 2018, 'White', 'KIA456', 10);In order to use the datatable widget we need to feed itjson. Luckily this is easy to do in postgres
SELECT json_agg(t)::text FROM ( SELECT vehicle_id, make, model, year, color, registration_number, owner_id FROM vehicles) t;
Let’s make a table to copy to
CREATE TABLE public.vehicles_copy ( vehicle_id int4 NOT NULL, make varchar(50) NULL, model varchar(50) NULL, "year" int4 NULL, color varchar(30) NULL, registration_number varchar(20) NULL, owner_id int4 NULL, CONSTRAINT vehicles_copy_pkey PRIMARY KEY (vehicle_id), CONSTRAINT vehicles_copy_registration_number_key UNIQUE (registration_number));
Then a postgres function
Using TEXT input because Tulip doesn’t have a json data type (hmm maybe they should?)
CREATE OR REPLACE FUNCTION copy_vehicle_data(text_input TEXT)RETURNS VOID AS $DECLARE json_input JSONB; vehicle_ids INT[];BEGIN -- Convert text input to JSONB json_input := text_input::JSONB; -- Extract vehicle IDs to be updated and copied vehicle_ids := ARRAY(SELECT (value->>'vehicle_id')::int FROM jsonb_array_elements(json_input)); -- Insert into vehicles_copy by selecting from vehicles where vehicle_id matches the JSON input INSERT INTO public.vehicles_copy (vehicle_id, make, model, "year", color, registration_number, owner_id) SELECT v.vehicle_id, v.make, v.model, v."year", v.color, v.registration_number, v.owner_id FROM public.vehicles v WHERE v.vehicle_id = ANY(vehicle_ids) ON CONFLICT (vehicle_id) DO NOTHING; -- Update is_active in the source table UPDATE public.vehicles SET is_active = false WHERE vehicle_id = ANY(vehicle_ids);END;$ LANGUAGE plpgsql;
Just a little animation showing it in action
Thanks Aldo Utrera for the custom widget!