Fighting Views (CREATE VIEW) in PostgreSQL

    It so happened that the last 1.5 years I have been working with PostgreSQL, some tasks periodically arise that require a beautiful solution, I just talk about my own experience.

    Problem


    I have an ad plate with about 60 fields. Depending on the values ​​of these fields, an ad may appear in different places. This is usually solved by creating a view ( CREATE VIEW - manual). But this method has one significant drawback:
    • Adding a new column
    • Rename Column
    • Delete column

    It requires each time to recreate the view. This complicates the development and maintenance of the database.



    A small disclaimer: the entire database structure is made for an “example” and taken from the head. It shows how you can get the same effect without the above disadvantages and more. Yes, and everything that is written here is probably applicable to MySQL, those who wish can check.


    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 be dumped): Here it is clear that he fixed the list of columns and their names.
    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;



    EXPLAIN ANALYZE:


    EXPLAIN ANALYZE SELECT * FROM adv_working WHERE id = 123;

    Result: According to the query plan, it can be seen that the postgres "stuck together" two conditions into one: "(is_working AND (id = 123))"

    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




    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 tie for the number of columns.
    • You can rename other columns without fear.
    • You can safely delete columns.
    • 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. I need to change the tables during development. adding new features, and more. Nobody talks about the dynamic change in the structure of tables!

    Also popular now: