Postgresql – simple way in PL/pgSQL to check if a query returned no result

plpgsqlpostgresql

I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this:

select c.data into data from doc c where c.doc_id = id and c.group_cur > group_cur order by c.id desc limit 1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        select c.data into data from doc c where c.doc_id = id and c.global_cur > global_cur order by c.id desc limit 1;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RETURN NULL;

Best Answer

Exception blocks are meant for trapping errors, not checking conditions. In other words, if some condition can be handled at compile time, it should not be trapped as error but resolved by ordinary program logic.

In Trapping Errors section of PL/PgSQL documentation you can find such tip:

Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

Instead using exceptions (bad), or IF/THEN/ELSIF (better), you can rewrite this to one query:

SELECT c.data into data
FROM  doc c
WHERE c.doc_id = id
  and (
    c.group_cur > group_cur
    or
    c.global_cur > global_cur
  )
ORDER BY
  -- this will make group always preferred over global
  case when c.group_cur > group_cur then 1 else 2 end ASC,
  -- and this is your normal ordering
  c.id DESC
limit 1;

If you really want two queries, you can use special FOUND variable to test if previous query gave any result:

select c.data into data
from doc c
where c.doc_id = id and c.group_cur > group_cur
order by c.id desc limit 1;
if not found then
    select c.data into data
    from doc c
    where c.doc_id = id and c.global_cur > global_cur
    order by c.id desc limit 1;
    if not found then return null; end if;
end if;

Obligatory RTFM links folllow :-)

See this for description of FOUND variable, and this for IF/THEN blocks.

Related Solutions

PostgreSQL Query Performance – How to Return a Query Result Only if It Isn’t NULL

I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself.

Consider this simplified form:

CREATE FUNCTION store_distance(_lat    double precision
                              ,_long   double precision
                              ,_radius double precision
                              ,_tries  integer)
RETURNS TABLE(
   store_id   store.id%type
  ,store_name store.name%type
  ,distance   double precision) AS
$func$
DECLARE
   _ct  int   := 0;
   _pos point := point(_lat, _long);
BEGIN
   LOOP
      EXIT WHEN _ct >= _tries
           OR   EXISTS (
            SELECT 1 FROM store s
            WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius));

      _radius := _radius * 2;
      _ct     := _ct + 1;
   END LOOP;

   RETURN QUERY
   SELECT s.id, s.name
         ,get_distance(_lat, _long, s.latitude, s.longitude)
   FROM   store s
   WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius);
   ORDER  BY 3;
END
$func$ LANGUAGE plpgsql STRICT;

I made the function STRICT to disallow NULL input, which could result in an endless loop.

Note how I use circles with the "Contained" operator <@ instead of a boxes. One would assume calculations to be slightly more expensive than with boxes, but it hardly matters at all once you support your query with a GiST index like:

CREATE INDEX store_point_gist_idx ON store
USING gist (point(latitude, longitude));

You might consider to store lat / lon as point to begin with and replace the index on an expression with a simpler one on the column. Works either way, just make sure the query matches the index so it gets used. Big difference for big tables.

You may be interested in this closely related answer on SO I posted last year - with a lot more explanation and links.

PostgreSQL RETURN NEXT Function – How to Use

If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction.

Example:

CREATE FUNCTION example_cursor() RETURNS refcursor AS $$
DECLARE
  c refcursor;
BEGIN
  c:='mycursorname';
  OPEN c FOR select * from generate_series(1,100000);
  return c;                                       
end;
$$ language plpgsql;

Usage for the caller:

BEGIN;
SELECT example_cursor();
 [output: mycursor]
FETCH 10 FROM mycursor;

 Output:

 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10

CLOSE mycursor;
END;

When not interested in piecemeal retrieval, FETCH ALL FROM cursorname may also be used to stream all results to the caller in one step.

Best Answer

Related Solutions

PostgreSQL Query Performance – How to Return a Query Result Only if It Isn’t NULL

PostgreSQL RETURN NEXT Function – How to Use

Related Question