如何不返回从存储过程中找到的记录

Question

Is it possible to have a stored procedure behave exactly like a regular select query when no records are found, or is this a driver issue.

For example, with go, a query that returns no rows will return an sql.ErrNoRows error. However, this will not:

create table emptytable(id int);

create function selectany() returns emptytable as $$
DECLARE
  _out emptytable;
BEGIN
  SELECT * INTO emptytable FROM emptytable limit 1;
  RETURN _out;
END;
$$ LANGUAGE PLPGSQL;

I have tried SELECT INTO STRICT, and while that raises a "query returned no rows" error, it is not the same as a non-stored procedure query. Neither is raising NO_DATA_FOUND.

Answer 1

If I understand your requirements correctly:

Return one or no row from a function and allow to do more with the returned row (if any).

Test table:

CREATE TABLE emptytable(id int, txt text);  -- multiple columns

To return one or no complete table row:

CREATE OR REPLACE FUNCTION selectany_all()
  RETURNS SETOF emptytable AS
$func$
DECLARE
   _out  emptytable;
BEGIN
   FOR _out IN
      SELECT * FROM emptytable LIMIT 1
   LOOP
     -- do something with _out before returning
     RAISE NOTICE 'before: %', _out;
     RETURN NEXT _out;
     -- or do something with _out after returning row
     RAISE NOTICE 'after: %', _out;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

For a more flexible approach: return arbitrary columns:

CREATE OR REPLACE FUNCTION selectany_any()
  RETURNS TABLE (id int, txt text) AS
$func$
BEGIN
   FOR id, txt IN
      SELECT e.id, e.txt FROM emptytable e LIMIT 1
   LOOP  
     -- do something with id and text before returning
     RAISE NOTICE 'before: %, %', id, txt;
     RETURN NEXT;
     -- or do something with id and text after returning row
     RAISE NOTICE 'after: %, %', id, txt;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Note, the LOOP is never entered if there is no row. Accordingly you will get no NOTICE from my test code.

Both functions work for n rows returned as well, LIMIT 1 is just for this particular request.

Closely related, wtih more explanation:

• Return multiple fields as a record in PostgreSQL with PL/pgSQL