Postgresql views когда использовать

Как правильно использовать View в PostgreSQL?

Я знаю, как создавать View и как получать данные оттуда. Вопрос вот в чем: эти viewe —
1. это что-то вроде интерпретатора SQL команд? Когда я делаю запрос типа `SELECT * FROM mycustomview` мой запрос просто является оберткой для того запроса, который я написал при создании этого View? В таком случае, это только удобство, но не performance.
2. или View — это реальные таблицы, сформированные из всех данных, которые получены из того запроса, что я использовал при создании View? В таком случае это сильно влияет не только на удобство но и на performance.

Если View работает как интерпритатор (1), есть ли какие-то общепринятые подходы для того, чтобы делать то, что я описал под цифрой 2?
В последних версиях Postgres появляется все больше фишек для работы с json. Есть ли смысл создавать triggers или rules для того, чтобы данные из определенных таблиц складывались в новую специальную таблицу, чтобы избежать необходимости делать сложные запросы для получения этих данных?

CREATE TABLE "public"."user" ( "email" varchar(36) NOT NULL COLLATE "default", "password" varchar(16) NOT NULL COLLATE "default", "id" uuid NOT NULL DEFAULT uuid_generate_v4(), CONSTRAINT "User_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE ) WITH (OIDS=FALSE); ALTER TABLE "public"."user" OWNER TO "postgres"; CREATE UNIQUE INDEX "users_id_key" ON "public"."user" USING btree("id" ASC NULLS LAST); CREATE TABLE "public"."friend" ( "id" uuid NOT NULL DEFAULT uuid_generate_v4(), "name" varchar(36) NOT NULL COLLATE "default", CONSTRAINT "ability_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE, ) WITH (OIDS=FALSE); ALTER TABLE "public"."friend" OWNER TO "postgres"; CREATE UNIQUE INDEX "ability_id_key" ON "public"."friend" USING btree("id" ASC NULLS LAST); CREATE TABLE "public"."user_friend" ( "id" uuid NOT NULL DEFAULT uuid_generate_v4(), "owner" uuid NOT NULL, "friend" uuid NOT NULL, CONSTRAINT "ability_relation_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "owner" FOREIGN KEY ("owner") REFERENCES "public"."user" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT "friend" FOREIGN KEY ("friend") REFERENCES "public"."friend" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE ) WITH (OIDS=FALSE); ALTER TABLE "public"."user_friend" OWNER TO "postgres";

Например, мне необходимо постоянно делать запросы вроде такого:

select row_to_json(t) from ( select public.user.email, ( select array_to_json(array_agg(row_to_json(ability_relations))) from ( select * from public.friend where public.friend.id in ( select public.user_friend.friend from public.user_friend where public.user_friend.owner=public.user.id ) ) ability_relations ) as abilities from public.user ) t

Это не самый сложный запрос, дочерних таблиц привязанных с помощью Foreign Key может быть до 4 штук. следовательно, запрос будет в 4 раза тяжелее.
Запросы на получение этих данных в сотни и тысячи раз чаще чем запросы на изменение этих данных. Имеет ли смысл с помощью triggers или rules создавать специальные таблицы, которые будут наполняться собранными в json объекты данными?

Благодарю за уделенное время!

• Вопрос задан более трёх лет назад
• 4086 просмотров

PostgreSQL VIEW (представление)

В этом учебном пособии вы узнаете, как создавать, обновлять и удалять VIEWS в PostgreSQL с синтаксисом и примерами.

Что такое VIEW в PostgreSQL?

В PostgreSQL VIEW это не физическая таблица, а скорее виртуальная таблица, созданная запросом joins, соединяющим одну или несколько таблиц.

Создать VIEW

Синтаксис

Синтаксис оператора CREATE VIEW в PostgreSQL:

CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];

OR REPLACE Необязательный. Если вы не укажете этот оператор, а VIEW уже существует, оператор CREATE VIEW вернет ошибку. view_name Имя VIEW, которое вы хотите создать в PostgreSQL. WHERE conditions Необязательный. Условия, которые должны быть выполнены для включения записей в VIEW.

Пример

Вот пример того, как использовать оператор CREATE VIEW для создания представления в PostgreSQL:

CREATE VIEW current_inventory AS
SELECT product_name, quantity
FROM products
WHERE quantity > 0;

Этот пример CREATE VIEW создаст виртуальную таблицу на основе результирующего набора оператора SELECT. Теперь вы можете запросить PostgreSQL VIEW следующим образом:

FROM current_inventory;

Обновить VIEW

Вы можете изменить определение VIEW в PostgreSQL, не удаляя его, используя оператор CREATE OR REPLACE VIEW.

Синтаксис

Синтаксис для оператора CREATE OR REPLACE VIEW в PostgreSQL:

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;
view_name Название представления, которое вы хотите обновить.

Пример

Вот пример того, как может использоваться оператор CREATE OR REPLACE VIEW в PostgreSQL:

CREATE or REPLACE VIEW current_inventory AS
SELECT product_name, quantity, category_name
FROM products
INNER JOIN categories
ON products.category_id = categories.category_id
WHERE quantity > 0;

Этот пример CREATE OR REPLACE VIEW обновил бы определение VIEW с именем current_inventory , не удаляя его.

ВНИМАНИЕ:
Оператор CREATE OR REPLACE VIEW будет работать, если вы добавляете столбцы в представление в конце списка. Тем не менее, это будет ошибка, если вы добавляете новые столбцы в существующие столбцы (то есть: начало или середина существующего списка).
В этом случае не используйте оператор CREATE OR REPLACE VIEW . Лучше удалить VIEW и использовать оператор CREATE VIEW !

Удалить VIEW

После создания VIEW в PostgreSQL вы можете удалить его с помощью оператора Drop VIEW.

Синтаксис

Синтаксис для оператора Drop VIEW в PostgreSQL:

Drop VIEW [IF EXISTS] view_name;

view_name Название представления, которое вы хотите удалить. IF EXISTS Необязательный. Если вы не укажете этот параметр и VIEW не существует, оператор Drop VIEW вернет ошибку.

Пример

Вот пример того, как использовать оператор Drop VIEW в PostgreSQL:

PostgreSQL, как создать VIEW (представление)

В PostgreSQL, view — это виртуальная таблица (представление). Она хранит результат выполнения SELECT запроса. С помощью вьюхи можно сохранить SELECT запрос и получить быстрый доступ к часто используемым данным.

Создание представление (view) — это альтернативное решение. У него есть свои плюсы и минусы.

Как создать вью в PostgreSQL

Создать вьюху в постгрес намного проще, чем хранимую процедуру. Чтобы сохранить результат SELECT запроса в представление, нужно написать

CREATE VIEW view_name AS 

Замени view_name на что-то, что больше похоже на данные которые ты сохраняешь и после ключевого слова AS напиши свой SELECT запрос.

Если мы представим, что у нас есть таблица users и у каждого юзера есть поле follower_count , то нам может быть полезно создать вью popular_users и включить туда всех пользователей у которых более 1000 подписчиков.

CREATE VIEW influencers AS SELECT name, email, created_at FROM users WHERE follower_count > 1000; 

Сложность SELECT запроса может быть любой. Тебя никто не ограничивает. Можешь добавить джойны, группировки или еще что-то. Единственное ограничение в том, что команда ORDER BY не может использоваться в PostgreSQL вью.

Вьюха будет выглядеть как таблица в твоей базе данных. Это очень удобно если ты пользуешься GUI клиентом.

Как удалить вью в PostgreSQL

Чтобы удалить представление в PostgreSQL используется похожий на удаление таблицы синтаксис:

DROP view [ IF EXISTS ] view_name; 

Флаг IF EXISTS не обязателен. Его полезно добавить, чтобы защититься от ошибок, которые появятся если ты случайно сделаешь опечатку в имени вьюхи view_name .

SQL-Ex blog

Эта статья поможет вам понять, что такое представление в базе данных в общем, и что такое представление конкретно в PostgreSQL. Вы научитесь создавать/писать запросы/удалять представление в PostgreSQL, осваивая соответствующий синтаксис на рабочих примерах, используя для этого терминал Psql или инструмент PgAdmin.

Что такое представление?

Представление в SQL можно рассматривать как виртуальную таблицу, которая основана на результирующем наборе оператора SQL. Подобно реальной таблице представление состоит из строк и столбцов, и оно может включать данные из одной или большего числа таблиц. Представление не содержит каких либо данных, которые нужно было бы физически хранить, т.е. оно не сохраняется в памяти и фактически не занимает места в хранилище. Подобно таблице SQL, имя представления должно быть уникальным в пределах базы данных. Так как представлению назначаются отдельные разрешения, представления могут использоваться для ограничения доступа к таблицам, так что пользователи видят только конкретные строки или столбцы таблицы.

Представление в PostgreSQL удовлетворяет всем упомянутым характеристикам и может быть создано с помощью нижеприведенного синтаксиса:

CREATE [TEMP | TEMPORARY] VIEW имя_представления AS 
SELECT столбец1, столбец2. 
FROM имя_таблицы 
WHERE [условие];

Здесь имя представления указывается после ключевых слов ‘CREATE VIEW’, после чего следуют требуемые столбцы из таблицы, а затем условие WHERE. Ключевое слово ‘Temp/Temporary’ не является обязательным и задается, если создается временное представление. Временное представление прекращает существование в конце текущего сеанса.

Пример:

CREATE VIEW STOCK_VIEW AS 
SELECT stock_id, stock_name, stock_price 
FROM stocks;

Вывод списка представлений в терминале Psql:
Для вывода всех представлений в схеме PostgreSQL может использоваться команда ‘\dv’.

Создание представления в PgAdmin

Для создания представления в PgAdmin нужно перейти в раздел меню ‘views->Create->View’, как показано ниже:

Список представлений в PgAdmin

После создания представления либо в терминале Psql, либо в PgAdmin, его можно увидеть в списке ‘Views’ готовым для использования, как показано ниже.

Запрос к представлению

Запрос на выборку данных из представления PostgreSQL подобен запросу из таблицы. Вот синтаксис:

Select * from stock_view;

Запрос к представлению в PgAdmin

Для запроса к представлению в PgAdmin нужно перейти в меню ‘Views->View->View/Edit Data’. Затем пользователю будет предложено несколько вариантов, из которых пользователь может сделать выбор на основе его требований и получить данные. Заметим, что хотя опция здесь читается как ‘View/Edit Data’ (просмотр/редактирование данных), представления не могут редактироваться. Однако если обновляются данные в исходной таблице, это также отражается в представлении.

Удаление представления

Запрос на удаление представления простой и короткий. Чтобы удалить представление, укажите имя представления сразу после ключевых слов ‘DROP VIEW’.

DROP VIEW имя_представления;

Удаление представления в PgAdmin

Чтобы удалить представление в PgAdmin, необходимо перейти в меню ‘Views->View->Delete/Drop’.