How to return a value per WHERE conditions in postgresql SELECT in single query?

Question

I have a table like this :

CREATE TABLE foo_table (
    a ... ,
    b ... ,
    c ... ,
);

ALTER TABLE ONLY foo_table
    ADD CONSTRAINT unique_constraint UNIQUE (a,b);

Now ,I'm looking to write a postgresql SELECT in single query with someWHERE conditions to do the following :

if (a and b and c) :
    return 1
elif not ( a and b) :
    return 2
elif not ( a and b and c) :
    return 3

What is the best way? my PostgreSQL version is 9.6.6

Possible duplicate of [SQL Case Statement Syntax?](https://stackoverflow.com/questions/4622/sql-case-statement-syntax) — Jorge Campos, Jan 08 '18 at 14:15
Notice that the answer I chose as duplicate is for SQL Server but since CASE statement is SQL ANSI it will fit exactly the same for PostgreSQL — Jorge Campos, Jan 08 '18 at 14:16
The question does not make sense (yet) because only `(a and b and c)` qualifies in the `WHERE` condition. There is probably more to it. Do you retrieve a single row or many rows? Provide more context, and always your Postgres version and table definition. — Erwin Brandstetter, Jan 08 '18 at 15:35

score 0 · Answer 1 · answered Jan 08 '18 at 14:22

Make a table function which accepts as parameters those 3 conditions.

I am guessing the returning value is partition of the given table, so you could easily reconfigure it and add some logic into. And at the end, you could fetch your results with simple querying the function.

How to return a value per WHERE conditions in postgresql SELECT in single query?

1 Answers1