The suppression of the views(CREATE VIEW) in PostgreSQL
it So happened that the last 1.5 years I am working with PostgreSQL, occasionally there are some tasks that require an elegant solution, I'll just tell about my own experience.
the
Problem
I have a plaque of ad, it has about 60 fields. Depending on the values of these fields, an ad can be show in different places. Usually this is solved by creating a view(CREATE VIEW manual). But this method has one major drawback:
the
-
the
- to add a new column the
- Rename column the
- Delete column
It needs to recreate the view. This complicates the development and maintenance of database.
/ >
a Small disclaimer: the entire structure of the database is made for "example" and comes from the head. Here is how you can get the same effect without the above mentioned disadvantages. And all that is written here might apply to MySQL, anyone can check.
the
Search solutions
the
Structure:
the
CREATE TABLE adv (
id INT,
title TEXT
info_short TEXT
info_full TEXT
is_working BOOLEAN
can_display BOOLEAN
);
the
Performance:
the
CREATE VIEW adv_working
AS
SELECT *
FROM adv
WHERE is_working;
the
Dump:
Postgres "deployed" in this view(so it will danitsa):
the
CREATE OR REPLACE VIEW "public"."adv_working" (
id
title,
info_short,
info_full
is_working
can_display)
AS
SELECT adv.id, adv.title adv.info_short, adv.info_full, adv.is_working, adv.can_display
FROM adv
WHERE adv.is_working;Here you can see that he recorded a list of columns and their names.
the
EXPLAIN ANALYZE:
the
EXPLAIN ANALYZE SELECT * FROM adv_working WHERE id = 123;The result:
the
QUERY PLAN
Seq Scan on adv (cost=0.00..17.50 rows=2 width=106) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (is_working AND (id = 123))
Total runtime: 0.028 ms
The query plan shows that Postgres "glued" the two conditions into one: "(is_working AND (id = 123))"
My solution:
the
Write a procedure to clean the filter SQL:
the
CREATE OR REPLACE FUNCTION "public".adv_filter_only_working (adv) RETURNS boolean AS
$body$
SELECT $1.is_working
$body$
LANGUAGE 'sql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
the
EXPLAIN ANALYZE:
the
EXPLAIN ANALYZE SELECT * FROM adv WHERE id = 123 AND adv_filter_only_working(adv);The result:
the
QUERY PLAN
Seq Scan on adv (cost=0.00..17.50 rows=2 width=106) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (is_working AND (id = 123))
Total runtime: 0.061 ms
the
As you can see, the effect is the same, but there are advantages:
the
-
the
- with No ties to the number of columns. the
- to Rename other columns it is safe. the
- to Delete the columns also can. the
- you Can create a functional index, but this logic will still be in one place(no need to duplicate it in the index).
UPD: Looks like I didn't understand. The table I need to change during development. add new features only. About dynamic change in the structure of the tables, nobody speaks!
Комментарии
Отправить комментарий