CODEWITHSUNDEEP
sqlpostgresqlselect
user1298426
user1298426

Reputation: 3717

How to write postgres query for below issue?

      Level1         |    Level2      |     Level3          |     Level4
key       |  value   | key     |value |    key     | value  |  key     | value
---------------------|----------------|---------------------|-----------------  
setting1  |  true    |                |  setting1  |  true  |setting1  | false
                     |setting2 | false|                     |setting2  | false    
                     |                |                     |setting3  | true

I have 4 tables. Level1, Level2, Level3, Level4. Each has key value pair.

I want to get key, value pair such that if key value pair is present in level1 then it should not check level2,3,4.

If it's not present in level1 then it should go to level2 if not then level3.

Value will always be present in level4.

So final o/p is of type map.

key       |  value
---------------------
setting1  |  true
setting2  |  false
setting3  |  true

Is it possible with sql query or do I need to write function or procedure for it?

Upvotes: 0

Views: 51

Answers (3)

Richard Huxton
Richard Huxton

Reputation: 22893

You want DISTINCT ON with UNION ALL (although a straight UNION would work here too)

BEGIN;

CREATE TEMP TABLE settings1 (key text, value text, PRIMARY KEY (key));
CREATE TEMP TABLE settings2 (key text, value text, PRIMARY KEY (key));
CREATE TEMP TABLE settings3 (key text, value text, PRIMARY KEY (key));

INSERT INTO settings1 VALUES ('a', 'a1');
INSERT INTO settings2 VALUES ('a', 'a2');
INSERT INTO settings2 VALUES ('b', 'b2');
INSERT INTO settings3 VALUES ('b', 'b3');
INSERT INTO settings3 VALUES ('c', 'c3');

SELECT DISTINCT ON (key) key, value FROM (
    SELECT 1 AS lvl, key, value FROM settings1
    UNION ALL
    SELECT 2 AS lvl, key, value FROM settings2
    UNION ALL
    SELECT 3 AS lvl, key, value FROM settings3
    ORDER BY key, lvl
) AS settings;

ROLLBACK;

Gives:

 key | value 
-----+-------
 a   | a1
 b   | b2
 c   | c3
(3 rows)

Upvotes: 1

gpeche
gpeche

Reputation: 22504

SQL is declarative so it is hard to do side effects in a query: if you need that exact behaviour you should implement it in a procedural language.

If all you care about is that the query is functionally equivalent to what you described, you can use the following query:

    select key, value from (
        select 1 as precedence, key, value from t1
        union all
        select 2 as precedence, key, value from t2
        union all
        select 3 as precedence, key, value from t3
        union all
        select 4 as precedence, key, value from t4
    )
    where key = <searched key>
    order by precedence asc
    limit 1

However, the side effect of only accessing the tables as needed is not guaranteed: the SQL engine is free to access the tables whenever it thinks it's appropiate

Upvotes: 1

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

One approach would be to use CASE - WHEN structure.

See this SQLFiddle

SELECT CASE WHEN t1."key" IS NOT NULL
            THEN t1."key"
            ELSE CASE WHEN t2."key" IS NOT NULL
                      THEN t2."key"
                      ELSE CASE WHEN t3."key" IS NOT NULL
                                THEN t3."key"
                                ELSE t4."key"
                           END
                 END
       END
       as "key",
       CASE WHEN t1."value" IS NOT NULL
            THEN t1."value"
            ELSE CASE WHEN t2."value" IS NOT NULL
                      THEN t2."value"
                      ELSE CASE WHEN t3."value" IS NOT NULL
                                THEN t3."value"
                                ELSE t4."value"
                           END
                 END
       END
       as "value"
FROM table4 t4
LEFT JOIN table1 t1 ON t4."key" = t1."key"
LEFT JOIN table2 t2 ON t4."key" = t2."key"
LEFT JOIN table3 t3 ON t4."key" = t3."key"

Upvotes: 0

Related Questions