SQLize | PHPize | SQLtest

CREATE TABLE users ( id serial primary key, login text, mass integer, -- вес в кг height numeric -- рост в метрах ); INSERT INTO users (login, mass, height) VALUES ('U001', 85, 1.75); CREATE TABLE users_bonus ( user_id integer primary key references users(id), bonus integer -- бонус за workouts ); CREATE TABLE workouts ( id serial primary key, user_id integer references users(id), total_distance integer ); INSERT INTO workouts (user_id) VALUES (1); CREATE TABLE distances ( id serial primary key, workout_id integer references workouts(id), distance integer, date_time timestamp default current_timestamp ); INSERT INTO distances (workout_id, distance) VALUES (1, 5100); CREATE PROCEDURE set_user_bonus( p_user_id integer, p_daily_distance integer ) LANGUAGE plpgsql AS $$ DECLARE _bonus integer; BEGIN _bonus = (p_daily_distance / 1000)::integer; INSERT INTO users_bonus (user_id, bonus) VALUES (p_user_id, _bonus) ON CONFLICT (user_id) DO UPDATE SET bonus = users_bonus.bonus + _bonus; END; $$; CREATE PROCEDURE set_daily_bonus(p_date date) LANGUAGE plpgsql AS $$ DECLARE _r record; BEGIN FOR _r IN SELECT user_id, SUM(distance)::integer daily_distance FROM workouts JOIN distances ON workouts.id = distances.workout_id WHERE date_time::date = p_date GROUP BY user_id LOOP CALL set_user_bonus(_r.user_id, _r.daily_distance); END LOOP; END; $$; CALL set_daily_bonus(current_date); select *, mass / (height * height) bmi from users join users_bonus on users_bonus.user_id = users.id; CREATE FUNCTION test () RETURNS integer LANGUAGE plpgsql AS $$ DECLARE _res INT := 0; BEGIN FOR _i IN 1..10 BY 3 LOOP INSERT INTO users (login) VALUES (_i); _res = _res + _i; END LOOP; RETURN _res; END $$; SELECT * FROM test(); select *, mass / (height * height) bmi from users left join users_bonus on users_bonus.user_id = users.id;