Is there a simple way in PL/pgSQL to check if a query returned no result?

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;

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.

StackExchange Database Administrators Q#10492, answer score: 25