8

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.

1
  • That's actually a very interesting question. Commented Apr 26, 2014 at 19:20

2 Answers 2

8

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.

3
  • You might be interested in my added answer since I am referring to yours. Commented Apr 26, 2014 at 19:49
  • 1
    @ErwinBrandstetter I am always wondering how you keep finding these ancient questions :) Commented Apr 28, 2014 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. :) Commented Apr 28, 2014 at 14:39
8

The return set

The manual:

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
  LANGUAGE plpgsql 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$;

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 PL/pgSQL.

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][4]. 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
  LANGUAGE plpgsql 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$;

Create a wrapper function, which you actually call:

CREATE OR REPLACE FUNCTION f_min_records_wrapper(min_ct integer = 10)
  RETURNS SETOF t
  LANGUAGE plpgsql 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$;

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
  LANGUAGE plpgsql 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$;

SQL Fiddle demonstrating all.

Feature Wish - non-existent at least up to pg 16

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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