Форум программистов, компьютерный форум CyberForum.ru

PostgreSQL

Войти
Регистрация
Восстановить пароль
 
mstdmstd
0 / 0 / 0
Регистрация: 21.02.2010
Сообщений: 123
#1

Нужна практика с postgresql - PostgreSQL

27.12.2016, 11:56. Просмотров 233. Ответов 0
Метки нет (Все метки)

Всем привет,
Осваивая postgresql ( 9.4.10 ) пишу админку для небольшого сайта с продуктами, примерно с таким набором таблиц :
  • attribute
  • attribute_item
  • category
  • product
  • product_attribute
  • product_category
  • product_comment
  • product_downloadable
  • product_image
  • product_tag
  • tag

Также для формирования прайс листа и для операций CRUD-а я использую sql-функции (ниже).
Ордера пока не делаю.
Интересует ваше мнение о функциях ниже и подскажите, пожалуйста, какие еще задачи можно придумать и поделать для практики
с postgresql ? Можно с использованием расширений sql(jsonb...).



Мои функции ипользуемые для формирования прайс листа и для CRUD-операций:
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
CREATE OR REPLACE FUNCTION public.pd_get_product_prices(
    p_limit INTEGER,
    p_offset INTEGER,
    p_title CHARACTER VARYING DEFAULT NULL,
    p_status type_productstatus DEFAULT NULL,
    p_in_stock BOOLEAN DEFAULT NULL,
    p_sku CHARACTER VARYING DEFAULT NULL,
    p_sale_price_from type_money DEFAULT NULL,
    p_sale_price_till type_money DEFAULT NULL,
    p_rating_from INTEGER DEFAULT NULL,
    p_rating_till INTEGER DEFAULT NULL,
    p_category_list INTEGER[] DEFAULT NULL
)
 
 RETURNS TABLE(id INTEGER, title CHARACTER VARYING, STATUS type_productstatus, sku CHARACTER VARYING, user_id SMALLINT, regular_price type_money, sale_price type_money, in_stock BOOLEAN, short_description CHARACTER VARYING, virtual BOOLEAN, rating_count INTEGER, rating_summary INTEGER, rating INTEGER, created_at TIMESTAMP WITHOUT TIME zone, product_image CHARACTER VARYING, product_categories CHARACTER VARYING[], product_categories_id SMALLINT[], product_tags CHARACTER VARYING[], product_tags_id SMALLINT[], product_attributes jsonb)
 LANGUAGE SQL
AS $function$
 
SELECT * FROM ( SELECT p.id, p.title, p.status, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.virtual, p.rating_count, p.rating_summary,
CAST( FLOOR( p.rating_summary / NULLIF(p.rating_count,0)  ) AS INTEGER ) AS rating, p.created_at,
(SELECT pi.image FROM pd_product_image AS pi WHERE pi.product_id = p.id AND pi.is_main = TRUE ) AS product_image,
( SELECT array_agg(c.name) FROM pd_category AS c, pd_product_category AS pd WHERE c.id= pd.category_id AND pd.product_id = p.id) AS product_categories,
( SELECT array_agg(c.id) FROM pd_category AS c, pd_product_category AS pd WHERE c.id= pd.category_id AND pd.product_id = p.id) AS product_categories_id,
( SELECT array_agg(t.name) FROM pd_tag AS t, pd_product_tag AS pt WHERE t.id= pt.tag_id AND pt.product_id = p.id) AS product_tags,
( SELECT array_agg(t.id) FROM pd_tag AS t, pd_product_tag AS pt WHERE t.id= pt.tag_id AND pt.product_id = p.id) AS product_tags_id,
( SELECT pa.attributes_data FROM pd_product_attribute AS pa WHERE pa.product_id = p.id) AS product_attributes
 
 
FROM pd_product AS p LEFT JOIN  pd_product_category AS pc ON pc.product_id = p.id
       WHERE
           ( p.status= p_status OR p_status IS NULL ) AND
           ( p.in_stock = p_in_stock OR p_in_stock IS NULL )  AND
           ( p.sku LIKE p_sku OR p_sku IS NULL )  AND
           ( p.title LIKE p_title OR p_title IS NULL ) AND
           ( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS NULL ) ) AND
          ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
) AS ROWS
 
       WHERE( (   ROWS.rating >= p_rating_from AND ROWS.rating <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS NULL ) )
 
       ORDER BY ROWS.sale_price ASC
       LIMIT p_limit  OFFSET p_offset;
 
$function$
 
 
CREATE OR REPLACE FUNCTION public.pd_update_product(p_id INTEGER, p_title CHARACTER VARYING, p_status type_productstatus, p_sku CHARACTER VARYING, p_user_id INTEGER, p_regular_price type_money, p_sale_price type_money, p_in_stock BOOLEAN, p_is_homepage BOOLEAN, p_is_featured BOOLEAN, p_short_description CHARACTER VARYING, p_description text, p_has_attributes BOOLEAN, p_downloadable BOOLEAN, p_virtual BOOLEAN, p_category_list INTEGER[] DEFAULT NULL::INTEGER[], p_tag_list INTEGER[] DEFAULT NULL::INTEGER[], p_attribute_list jsonb DEFAULT NULL::jsonb, p_created_at TIMESTAMP WITHOUT TIME zone DEFAULT NULL::TIMESTAMP WITHOUT TIME zone)
 RETURNS INTEGER
 LANGUAGE plpgsql
AS $function$
  BEGIN
    IF p_id <= 0 THEN
 
      INSERT INTO pd_product ( title, STATUS, sku, user_id, regular_price, sale_price, in_stock, is_homepage, is_featured, short_description, description, has_attributes, downloadable, virtual )
        VALUES(  p_title, p_status, p_sku, p_user_id, p_regular_price, p_sale_price, p_in_stock, p_is_homepage, p_is_featured, p_short_description, p_description, p_has_attributes, p_downloadable, p_virtual );
      p_id= lastval();
 
      IF (p_created_at IS NOT NULL ) THEN
        UPDATE pd_product SET created_at= p_created_at
          WHERE id= p_id;
      END IF;
 
    ELSE
 
      UPDATE pd_product SET title= p_title, STATUS= p_status, sku= p_sku, user_id= p_user_id, regular_price= p_regular_price, sale_price= p_sale_price, in_stock= p_in_stock, is_homepage= p_is_homepage, is_featured= p_is_featured, short_description= p_short_description, description= p_description, has_attributes= p_has_attributes, downloadable= p_downloadable, virtual= p_virtual, updated_at= now()
        WHERE id= p_id;
 
        IF p_downloadable = FALSE THEN
          DELETE FROM pd_product_downloadable WHERE product_id= p_id;
        END IF;
 
        IF p_has_attributes = FALSE THEN
          DELETE FROM pd_product_attribute WHERE product_id= p_id;
        END IF;
 
    END IF;
 
    PERFORM pd_update_product_category(p_id, p_category_list);
 
    PERFORM pd_update_product_tag(p_id, p_tag_list);
 
    PERFORM pd_update_product_attribute(p_id, p_attribute_list);
 
    RETURN p_id;
 
  END
$function$
 
 
CREATE OR REPLACE FUNCTION public.pd_delete_product(p_id INTEGER)
 RETURNS INTEGER
 LANGUAGE plpgsql
AS $function$
  BEGIN
 
      DELETE FROM pd_product_attribute WHERE product_id= p_id;
 
      DELETE FROM pd_product_category WHERE product_id= p_id;
 
      DELETE FROM pd_product_comment WHERE product_id= p_id;
 
      DELETE FROM pd_product_downloadable WHERE product_id= p_id;
 
      DELETE FROM pd_product_image WHERE product_id= p_id;
 
      DELETE FROM pd_product_tag WHERE product_id= p_id;
 
      DELETE FROM pd_product WHERE id= p_id;
 
      RETURN p_id;
 
  END
$function$
 
 
 
CREATE OR REPLACE FUNCTION public.pd_delete_product_comment(p_id INTEGER)
 RETURNS INTEGER
 LANGUAGE plpgsql
AS $function$
DECLARE
  r pd_product_comment%rowtype;
  BEGIN
 
      FOR r IN SELECT id FROM pd_product_comment WHERE parent_product_comment_id = p_id loop
          -- INSERT INTO pd_d(val) VALUES(  CAST( r.parent_product_comment_id as varchar(255)  )  );
          PERFORM pd_delete_product_comment(r.id);
 
      END loop;
 
      DELETE FROM pd_product_comment WHERE id= p_id;
 
      RETURN p_id;
 
  END
$function$
 
 
CREATE OR REPLACE FUNCTION public.pd_recalc_product_rating(p_id INTEGER, p_write_to_product BOOLEAN)
 RETURNS TABLE(comment_rating_count INTEGER, comment_rating_sum INTEGER)
 LANGUAGE plpgsql
AS $function$
DECLARE comment_rating_count INTEGER;
DECLARE comment_rating_sum INTEGER;
   BEGIN
 
       SELECT COUNT("id"), SUM(rating) INTO comment_rating_count, comment_rating_sum FROM pd_product_comment WHERE product_id = p_id LIMIT 1;
       IF ( p_write_to_product ) THEN
          UPDATE pd_product  SET rating_count = comment_rating_count, rating_summary= COALESCE(comment_rating_sum,0) WHERE id = p_id;
       END IF;
       RETURN QUERY SELECT comment_rating_count, COALESCE( comment_rating_sum,0 ) ;
 
    END
$function$
Спасибо!
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
27.12.2016, 11:56     Нужна практика с postgresql
Посмотрите здесь:

Конвертировать PostgreSQL - PostgreSQL
Добрый день! Необходимо конвертировать БД PostgreSQL в MS Access. БД весит чуть больше 6 Гб и включает турецкие символы. Буду рад любой...

Auto_increment postgresql - PostgreSQL
Узнал что в postgresql есть serial и bigserial,посмотрел реализацию,там все выглядит как костыль,неужели нет нормальных методов реализации?

Проблемы в PostgreSQL - PostgreSQL
Здраствуйте. Создаю проект в KDevelop , работаю с базой даных . Использую PostgreSQL , и соответственный синтаксис . Есть уже...

Android & postgreSQL - PostgreSQL
как связать пострес с андроидом???

Программа администрирования PostgreSQL - PostgreSQL
Доброго времени суток уважаемые! Поделитесь пожалуйста опытом, какую программу для администрирования PostgreSQL лучше использовать (и...

Эквивалент мультиудаления на PostgreSQL - PostgreSQL
Здравствуйте. Помогите пож создать эквивалент сего: DELETE a, b FROM wp_options a, wp_options b WHERE a.option_name LIKE...

Разбиение (секционирование) в PostgreSQL - PostgreSQL
в общем есть база на 29 млн строк. в нее идет постоянная запись и из нее нужно тоже постоянно получать данные. партицирование вроде...

Типы индексов в PostgreSQL - PostgreSQL
Какие существуют типы индексов в PostgreSQL,зашел в официальную документацию а там написано только &quot;B-tree, Hash, GiST and GIN&quot; а как же...

Доступ к PostgreSQL из Си с libpq - PostgreSQL
Доступ к PostgreSQL из Си при помощи libpq. Как мне это решить?

Не могу запустить PostgreSQL - PostgreSQL
Не могу запустить службу PostgreSQL на Windows Вот, что пишут! Пробовал запустить через Администратора, всё равно пишут тоже... . Может...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
После регистрации реклама в сообщениях будет скрыта и будут доступны все возможности форума.
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Рейтинг@Mail.ru