PostgreSQL conditional statement

Question

I need to implement a conditional SELECT statement based on the result of another statement. I don't want to create a function for this purpose, but simply using conditional SELECT.

My unsuccessful approach looks as below:

DO
$do$
BEGIN
IF SELECT count(*) FROM table1 < 1 THEN
   SELECT * FROM table2;
   RETURN QUERY;

ELSE 
   SELECT * FROM table3;
   RETURN QUERY;
END IF;
END
$do$

I could accomplish the same result by checking the results of my query in PHP and based on this, perform another query, yet I'm wondering if it's possible to do this solely in PostgreSQL.

@NickBarnes I edited the post: first query has to count results and based on that another query has to return results. All queries in my particular case are quite long, so I decided to replace them with simpler equivalent. If you ask about columns, all 3 queries will have the same columns — jacek_podwysocki
– jacek_podwysocki, Commented Jun 15, 2015 at 10:59
return query is a single statement. It should be return query select ... - but a PL/pgSQL block can't return anything so you can't use return query in there anyway — user330315
– user330315, Commented Jun 15, 2015 at 10:59
@a_horse_with_no_name good point, is there any possible way to make PL/pgSQL return a query? — jacek_podwysocki
– jacek_podwysocki, Commented Jun 15, 2015 at 11:01
You can make a PL/PgSQL function return something, but not an anonymous block. — user330315
– user330315, Commented Jun 15, 2015 at 11:10

Gregor Raýman · Accepted Answer · 2015-06-15 11:05:36Z

2

Try this:

with c as (select count(*) cnt from table1)
select table2.* from table2, c where c.cnt < 1
union all
select table3.* from table3, c where c.cnt >= 1

answered Jun 15, 2015 at 11:05

Gregor Raýman

3,09115 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

acesargl Over a year ago

In a general case if you want to know if a query does not exists is better NOT EXISTS than COUNT (*) < 1

Gregor Raýman Over a year ago

Yes. If the condition is only about existence (like in the question) then EXISTS is certainly preferable.

shA.t Over a year ago

@acesargl using with is preferred when you want to use a SELECT multiple times, you can also use with in your answer ;).

acesargl Over a year ago

Using count you force to count all the tuples, using NOT EXISTS when one tuple is found the inner SELECT is aborted (EXISTS and NOT EXISTS usually use short-circuit or McCarthy evaluation.

acesargl · Accepted Answer · 2015-06-15 11:07:52Z

2

If table2 and table3 are union-compatible then you can use a single SELECT:

SELECT * FROM table2 WHERE NOT EXISTS(SELECT * FROM table1)
UNION
SELECT * FROM table3 WHERE EXISTS(SELECT * FROM table1);

answered Jun 15, 2015 at 11:07

acesargl

5693 silver badges7 bronze badges

3 Comments

shA.t Over a year ago

As a performance issue use UNION ALL instead of UNION ;).

shA.t Over a year ago

I also suggest you to use SELECT 1 FROM table1 instead of SELECT * FROM table1 ;).

acesargl Over a year ago

Exists and not exists ignore the projection list in the nested select. So, in this case, select * and select 1 are equivalent.

Collectives™ on Stack Overflow

PostgreSQL conditional statement

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related