Setting up a PostgreSQL database

Useful#

Video:

• YouTube
• VK
• Dzen

Questions can be asked in the comments telegram: TG: fitgame

Database tables#

Structure users_social:

• id: integer id (AUTO_INCREMENT)
• social_type: character varying type platform (vk/ok/yandex)
• social_id: character varying id of platform

Structure users:

• id: integer id
• json_data: json basic data in JSON format
• points: integer player points, rating, etc.

Keys and indexes#

Keys and indexes in users_social:

• PRIMARY id (created automatically)
• UNIQUE social_type, social_id (for queries on these parameters)

Keys and indexes in users:

• PRIMARY id (for queries by id)
• INDEX points (for rating requests)

Create a profile#

Stored procedure for creating a player profile

CREATE OR REPLACE FUNCTION create_user(in_social json, in_user json)
RETURNS integer AS $$
DECLARE _user_id integer;
        in_inventory JSON;
BEGIN

  INSERT INTO users_social (social_type, social_id) VALUES(in_social->>'type',in_social->>'social_id') RETURNING id INTO _user_id;
  INSERT INTO users(id, json_data, points) VALUES(_user_id, (in_user->>'json_data')::json, ((in_user->>'json_data')::json->>'points')::int);

RETURN _user_id;

END;
$$ LANGUAGE plpgsql;

Request

select create_user('{"type": "vk", "social_id": 1}', '{"json_data": {"name": "FITGAME", "gold": 100, "energy": 50, "points": 100}}');

Getting a profile#

Stored procedure to get id based on site type and site id

CREATE OR REPLACE FUNCTION get_social_info(in_social JSONB)
RETURNS integer AS $$
DECLARE _user_id integer = 0;
BEGIN

  SELECT id INTO _user_id FROM users_social WHERE social_type = (in_social->>'type') AND social_id = (in_social->>'social_id');

  IF _user_id IS NULL THEN
    _user_id = 0;
  END IF;

  RETURN _user_id;

END;
$$ LANGUAGE plpgsql STABLE;

Request

select get_social_info('{"type":"vk", "social_id": "1"}');

Stored procedure to retrieve profile data based on ID

CREATE OR REPLACE FUNCTION get_user_info(in_user_id integer)
RETURNS JSONB AS $$
DECLARE out_json JSONB = '{}';
BEGIN

  SELECT row_to_json(row) INTO out_json FROM (SELECT * FROM users WHERE id = in_user_id) row;

  IF out_json IS NULL THEN
    out_json = '{}';
  END IF;

  RETURN out_json;

END;
$$ LANGUAGE plpgsql STABLE;

Request

select get_user_info(1);

Stored procedure to get all profile data based on site type and site ID

CREATE OR REPLACE FUNCTION get_user_info_2 (in_social JSONB)
RETURNS JSONB AS $$
DECLARE out_json JSONB = '{}';
BEGIN

  SELECT row_to_json(row) INTO out_json FROM (SELECT * FROM users JOIN users_social ON users_social.id = users.id WHERE social_type = (in_social->>'type') AND social_id = (in_social->>'social_id')) row;

  IF out_json IS NULL THEN
    out_json = '{}';
  END IF;

  RETURN out_json;

END;
$$ LANGUAGE plpgsql STABLE;

Request

select get_user_info_2('{"type":"vk", "social_id": "1"}');

Profile update#

Stored procedure to update player profile

CREATE OR REPLACE FUNCTION update_user(in_user_id integer, in_user JSONB)
RETURNS boolean AS $$
BEGIN

  UPDATE users SET 
    json_data =  (in_user->>'json_data')::json, points =  ((in_user->>'json_data')::json->>'points')::int
    WHERE id = in_user_id;
 

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Request

select update_user(1, '{"json_data": {"name": "FITGAME", "gold": 100, "energy": 50, "points": 300}}');

Top of the best#

Stored procedure to update player profile

CREATE OR REPLACE FUNCTION get_top(in_type text, in_val integer)
RETURNS JSONB AS $$
DECLARE out_json JSONB = '{}';
  json_list JSONB;
  place_num JSONB;
BEGIN

  IF in_type = 'points' THEN

    SELECT json_agg(row_to_json(t)) INTO json_list 
    FROM (
      SELECT json_data FROM users ORDER BY points DESC LIMIT 100
    ) t;

    SELECT count(1) INTO place_num FROM users WHERE points >= in_val;

  END IF;


  IF json_list IS NOT NULL THEN
    out_json = jsonb_set(out_json, '{list}', json_list, true);
  ELSE
    out_json = jsonb_set(out_json, '{list}', '[]', true);
  END IF;


  IF place_num IS NOT NULL THEN 
    out_json = jsonb_set(out_json, '{pos}', place_num, true);
  ELSE
    out_json = jsonb_set(out_json, '{pos}', -1, true);
  END IF;

  RETURN out_json;

END;
$$ LANGUAGE plpgsql STABLE;

Request

select get_top('points', 5);