Because PostgreSQL doesn't support dynamically calculated columns of a table, one possible way to overcome this limitation is to use views as it pointed in the answer to this question. I tried to implement such view in the following way:
CREATE VIEW user_info_view AS
SELECT *,
calculate_bonus_points_for_period(user_info.id) AS bonus_points_period,
calculate_user_level(user_info.id) AS level
FROM user_info;
Where user_info
table is the following:
CREATE TABLE user_info
(
id serial PRIMARY KEY,
card_id text NOT NULL UNIQUE,
name text NOT NULL,
address text NOT NULL,
phone text NOT NULL,
bank_credit bigint NOT NULL,
deposit bigint NOT NULL,
bonus_points bigint NOT NULL
);
And the 2 functions used to calculate view columns are the following:
CREATE TYPE user_level AS ENUM ('bronze', 'gold', 'platinum');
----------------------------------------------------------------------
CREATE FUNCTION calculate_bonus_points_for_period(user_id integer)
RETURNS bigint AS
$BODY$
DECLARE
bonus_period smallint;
result bigint;
BEGIN
SELECT settings.bonus_period INTO bonus_period FROM settings;
SELECT sum(bonus_points_earned) INTO result FROM game_pledges
WHERE game_pledges.user_id = calculate_bonus_points_for_period.user_id AND
ts > current_date - interval '1 day' * bonus_period;
RETURN COALESCE(result, 0);
END;
$BODY$
LANGUAGE plpgsql;
----------------------------------------------------------------------
CREATE FUNCTION calculate_user_level(user_id integer)
RETURNS user_level AS
$BODY$
DECLARE
bonus_points_period bigint;
bronze_gold_boundery bigint;
gold_platinum_boundery bigint;
BEGIN
SELECT user_info_view.bonus_points_period
INTO bonus_points_period
FROM user_info_view
WHERE user_id = id;
SELECT settings.bronze_gold_boundery INTO bronze_gold_boundery FROM settings;
SELECT settings.gold_platinum_boundery INTO gold_platinum_boundery FROM settings;
IF bonus_points_period >= gold_platinum_boundery THEN
RETURN 'platinum';
ELSIF bonus_points_period >= bronze_gold_boundery THEN
RETURN 'gold';
ELSE
RETURN 'bronze';
END IF;
END;
$BODY$
LANGUAGE plpgsql;
I have data in user_info
table but the view is empty when I opened it with pgAdmin
. In the beginning I have no data in game_pledges
table and I expected the sum
to be 0
. Can someone explain why the view is empty? I'm expecting there to be the same number of rows as in the user_info
table.
Afterwords:
When I executed SELECT count(*) FROM user_info_view;
I found that I have a few ambiguities in my functions. I corrected the code above with resolved ambiguities, but now I have another error:
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT: SQL statement "SELECT sum(bonus_points_earned) FROM game_pledges
WHERE game_pledges.user_id = calculate_bonus_points_for_period.user_id AND
ts > current_date - interval '1 day' * bonus_period"