0

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"
Community
  • 1
  • 1
bobeff
  • 3,543
  • 3
  • 34
  • 62
  • 1
    `RETURN coalesce(result,0);` If table is empty then `sum` is `null`. – Abelisto Nov 10 '16 at 17:52
  • 1
    Does `SELECT count(*) FROM user_info_view` return a different result from `SELECT * FROM user_info`? – Laurenz Albe Nov 11 '16 at 10:47
  • @Laurenz Albe 10x for the advice. I executed `SELECT * FROM user_info_view;` and I described the result in edited post (afterwords section). – bobeff Nov 11 '16 at 12:45
  • 1
    Try to debug this. If you omit the calculated column defined with `calculate_bonus_points_for_period`, does it work as expected? If you run the query that throws the error inside the function from psql, does it work? That error usually means that there is an infinite recursion with functions. Is there a context given for the error? – Laurenz Albe Nov 11 '16 at 12:58
  • I localized the problem to be in this part `SELECT user_info_view.bonus_points_period INTO bonus_points_period FROM user_info_view WHERE user_id = id;` in `calculate_user_level` function, but I can't understand why and how to rewrite it in correct manner. – bobeff Nov 14 '16 at 09:22
  • It seems that when I'm accessing another field in view in function which calculates the values in column of the same view happens infinite recursion, but how to overcome this? – bobeff Nov 14 '16 at 09:39

0 Answers0