Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do

RETURN QUERY EXECUTE 'some dynamic query'

And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to return them, but if it returns only < 10 rows, I don't want to return anything (empty set of ct_custom_type).

I tried:

RETURN QUERY EXECUTE 'some dynamic query'

GET DIAGNOSTICS variable = ROW_COUNT;
IF variable < 10 THEN

    # I don't know what to do here or how to accomplish this

END IF;

If I do RETURN QUERY SELECT 0, 0, ''::text; in the IF block (because ct_custom_type is a composite type of (integer, integer, text), it just adds this 'empty row' to previous query result, but I don't want to return anything in this case, I can do RETURN;, but it will return that previous result and I want to discard it.

I have it like this:

EXECUTE 'dynamic query';

GET DIAGNOSTICS variable = ROW_COUNT;
IF variable >= 10 THEN
    RETURN QUERY EXECUTE 'dynamic query';
END IF;

and it works, but I didn't want to do this query two times.

share|improve this question
    
That's actually a very interesting question. –  Erwin Brandstetter Apr 26 at 19:20

2 Answers 2

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.

share|improve this answer
    
You might be interested in my added answer since I am referring to yours. –  Erwin Brandstetter Apr 26 at 19:49
    
@ErwinBrandstetter I am always wondering how you keep finding these ancient questions :) –  dezso Apr 28 at 14:33
    
Mostly by looking for possible dupes of new questions or for a prior art I can link to in my answers. This time while tagging some typical questions with set-returning-functions. :) –  Erwin Brandstetter Apr 28 at 14:39

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 extended function from above:

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

   GET DIAGNOSTICS row_ct = ROW_COUNT;

   IF row_ct < min_ct THEN
      RAISE SQLSTATE 'BRRRR'   -- 5 ASCII chars
      USING MESSAGE = format('Only %s rows! Requested minimum was %s.', row_ct, min_ct);
   END IF;

END
$func$  LANGUAGE plpgsql;

Create a wrapper function, which you actually call:

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.