How to return no records found from a stored procedure

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.

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

2.5 options:

1a) If you just need to return a query, you can use SETOF and RETURN QUERY

1b) or just use language SQL as @ClodoaldoNeto, which returns a query natively using sql's SELECT stmt

2. If you need to process the result in the procedure, you must use SETOF and RETURN NEXT, ensuring you check IF FOUND THEN RETURN; (note lack of NEXT, which if given will act as a single blank row is returned)

Ideally, I'd like to not use SETOF for procedures known to return exactly none or 1 rows, but it seems SETOF is required to get a procedure to query like an sql statement from the app and have drivers recognize NO ROWS RETURNED

Examples below:

create table emptytable(id int);

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

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

As suggested in the comments do return setof emptytable

create function selectany()
returns setof emptytable as $$
    select *
    from emptytable 
    limit 1
    ;
$$ language sql;

Plain sql can do that