It so happened that for the last 1.5 years I have been working with PostgreSQL, periodically some problems arise that require a beautiful solution, I’ll just tell you about my own experience.

Problem

I have a label of ads, it has about 60 fields. Depending on the values ​​of these fields, the ad can be shown in different places. This is usually solved by creating a view ( CREATE VIEW - manual). But this method has one major drawback:

• Adding a new column
• Rename column
• Deleting a column

Requires re-create the view every time. This complicates the development and maintenance of the database.

A small disclaimer: the entire structure of the database is made for an “example” and is taken from the head. It shows how you can get the same effect without the above disadvantages and only. And everything that is written here may also apply to MySQL, those who wish can check it out.

')

Search for a solution

Structure:

CREATE TABLE adv (
id INT,
title TEXT,
info_short TEXT,
info_full TEXT,
is_working BOOLEAN,
can_display BOOLEAN
);


Representation:

CREATE VIEW adv_working
AS
SELECT *
FROM adv
WHERE is_working;


Dump:

Postgres "unfolded" this view in (so it will dump):
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 fixed the list of columns and their names.

EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM adv_working WHERE id = 123;

Result:

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


According to the request plan, it is clear that the postgres “stuck together” two conditions into one: "(is_working AND (id = 123))"

My decision:

Let's write the filter procedure in pure SQL:

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;


EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM adv WHERE id = 123 AND adv_filter_only_working(adv);
Result:
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


As you can see, the effect is the same, but there are pluses:

• No strings for the number of columns.
• You can rename other columns without fear.
• You can also delete the speakers safely.
• You can create a functional index, but the logic will still be in one place (no need to duplicate it in the index).

UPD: Looks like they didn't understand me. Tables I need to change during development. adding new features, and only. Nobody talks about dynamically changing the structure of tables!