PostgreSQL stored procedure to return rows or empty set

You can do something along the following lines:

test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer)
RETURNS SETOF integer AS
$body$
BEGIN
    CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS
    SELECT a
    FROM generate_series(1, cnt) t(a);

    IF (SELECT count(1) FROM tmp_container) > 5
    THEN
        RETURN QUERY SELECT a FROM tmp_container;
    END IF;
END;
$body$
LANGUAGE plpgsql;


test=> SELECT * FROM temptabl(4);
 temptabl 
----------
(0 rows)

test=> SELECT * FROM temptabl(6);
 temptabl 
----------
        1
        2
        3
        4
        5
        6
(6 rows)

This way you have to perform your original query only once. All other statements work on the temporary table.

The return set

Per documentation:

RETURN NEXT and RETURN QUERY do not actually return from the function — they simply append zero or more rows to the function's result set. Execution then continues with the next statement in the PL/pgSQL function. As successive RETURN NEXT or RETURN QUERY commands are executed, the result set is built up. A final RETURN, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function).

Raise EXCEPTION instead

So you can cancel the operation by raising an EXCEPTION and the client won't get any rows.
Won't get cheaper than that:

CREATE OR REPLACE FUNCTION f_min_records(min_ct integer = 10)  -- default minimum 10
  RETURNS SETOF tbl AS
$func$
DECLARE
   row_ct int;
BEGIN
   RETURN QUERY EXECUTE 'some dynamic query (matching return type)';

   GET DIAGNOSTICS row_ct = ROW_COUNT;

   IF row_ct < min_ct THEN
      RAISE EXCEPTION 'Only % rows! Requested minimum was %.', row_ct, min_ct;
   END IF;
END
$func$  LANGUAGE plpgsql;

Call (for default minimum of 10 rows):

SELECT * FROM f_min_records_wrapper();

We included a code example in the manual for that (get_available_flightid()).

Don't raise EXCEPTION

To my knowledge, the only way to keep the return set from actually being returned is to raise an exception. If you don't want to raise an exception, either, you are in a bit of a pickle with current plpgsql.

• If you trap the EXCEPTION in the same function, the result set is still returned.

• I tried and failed to solve it with a nested block. Doesn't seem to make any difference for the return set.

• But you can nest the function call in an outer function and catch the exception there. That works just as desired:

In addition to the above function:

CREATE OR REPLACE FUNCTION f_min_records_wrapper(min_ct integer = 10)
  RETURNS SETOF t AS
$func$
BEGIN
   RETURN QUERY
   SELECT * from f_min_records(min_ct);

EXCEPTION 
   WHEN SQLSTATE 'BRRRR' THEN
   RAISE NOTICE '%', SQLERRM;  -- optionally pass error msg
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_min_records_wrapper(17);

Alternative with temp table

Same basic idea like in deszo's answer, but avoid separate count, plus other features:

CREATE OR REPLACE FUNCTION f_temptbl(min_ct integer = 10)
  RETURNS SETOF t AS
$func$
DECLARE
   row_ct int;
BEGIN
   DROP TABLE IF EXISTS _temptbl;                      -- for mult. calls in 1 transaction
   CREATE TEMP TABLE _temptbl (LIKE t) ON COMMIT DROP; -- match RETURNS type

   EXECUTE 'INSERT INTO _temptbl SELECT * FROM t';     -- text with dyn SQL

   GET DIAGNOSTICS row_ct = ROW_COUNT;

   IF row_ct >= min_ct THEN
      RETURN QUERY TABLE _temptbl;
   END IF;
END;
$func$  LANGUAGE plpgsql;

SQL Fiddle demonstrating all.

Feature Wish - non-existent at present (including 9.4)

A command to cancel the return set would be marvelous:

RETURN CANCEL;

Or maybe even with an optional number of rows to "roll back" (default being all):

RETURN CANCEL 10;