Skip to content

Instantly share code, notes, and snippets.

@iolloyd
Created December 6, 2016 04:25
Show Gist options
  • Save iolloyd/5d63652b4edbcfeae70945bf3120fcfe to your computer and use it in GitHub Desktop.
Save iolloyd/5d63652b4edbcfeae70945bf3120fcfe to your computer and use it in GitHub Desktop.
Inserting multiple rows that are related to a parent table using postgresql functions
-- Schema (redacted)
CREATE TABLE set
(
workout_id INTEGER,
kgs NUMERIC NOT NULL,
reps INTEGER NOT NULL,
placement INTEGER NOT NULL,
exercise_id INTEGER,
CONSTRAINT set_workout_id_fkey FOREIGN KEY (workout_id) REFERENCES workout (id),
CONSTRAINT set_exercise_id_fkey FOREIGN KEY (exercise_id) REFERENCES exercise (id)
);
CREATE TABLE workout
(
id INTEGER PRIMARY KEY NOT NULL,
date DATE DEFAULT ('now'::text)::date,
client_id INTEGER,
CONSTRAINT workout_client_id_fkey FOREIGN KEY (client_id) REFERENCES client (id)
);
CREATE TABLE client
(
id INTEGER PRIMARY KEY NOT NULL,
nick TEXT,
email TEXT
);
CREATE TABLE exercise
(
id INTEGER PRIMARY KEY NOT NULL,
title TEXT NOT NULL,
display_name TEXT NOT NULL
);
CREATE OR REPLACE FUNCTION add_new_workout (
client INTEGER,
kgs REAL[],
reps INTEGER[],
placement INTEGER[],
exercise_id INTEGER[]
)
RETURNS VOID AS $$
BEGIN
WITH new_workout AS (
INSERT INTO workout (date, client_id) VALUES (now(), client)
RETURNING workout.id
)
INSERT INTO set (workout_id, exercise_id, kgs, reps, placement)
SELECT id, unnest(exercise_id), unnest(kgs), unnest(reps), unnest(placement)
FROM new_workout;
END;
$$ LANGUAGE plpgsql
;
-- Here is how we call the function
-- based on the following data
-- client_id: 1
-- sets:
-- 1. kgs: 100, reps: 8, exercise_id: 2
-- 2. kgs: 110, reps: 7, exercise_id: 2
SELECT add_new_workout(1, array[100, 110], array[8, 7], array[1,2], array[2, 2]);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment