1

I have a Postgres function that RETURNS TABLE. I want to return an empty table in the event that a CTE has zero rows. Something like

SELECT (CASE WHEN (SELECT count(1) FROM mycte = 0) 
        THEN (SELECT ... ??) -- not sure what to put here to return zero rows
        ELSE (SELECT ...)    -- A normal database query
        END)

How can I accomplish this? Just writing SELECT with nothing else returns a single column. SELECT x, y FROM () doesn't work. SELECT (SELECT , SELECT) doesn't work.

Kevin Burke
  • 61,194
  • 76
  • 188
  • 305

2 Answers2

3

If by "empty table" you mean no row, then just do not return anything.

In PL/pgSQL functions, just don't do anything (or execute a single RETURN;).

In SQL functions, add WHERE false or LIMIT 0 to your SELECT, so that no row is returned. Like:

SELECT -- your "ELSE" stuff here
WHERE  EXISTS (TABLE mycte);

Counting would be more expensive than EXISTS.
TABLE mycte is short for SELECT * FROM mycte. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

Give this a try:

WITH cte AS (
  SELECT c1
  FROM t1
)
SELECT *
FROM t1
WHERE EXISTS(SELECT 1 FROM cte) -- Only return rows from outer query if there are rows in your cte "table"

http://www.sqlfiddle.com/#!17/61913/15

ravioli
  • 3,749
  • 3
  • 14
  • 28