25

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;
icefex
  • 417
  • 1
  • 5
  • 9

2 Answers2

25

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.

filiprem
  • 6,539
  • 1
  • 18
  • 31
19

You can examine a special variable FOUND of a type boolean. From the documentation:

FOUND starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:

A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.

A PERFORM statement sets FOUND true if it produces (and discards) one or more rows, false if no row is produced.

UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.

A FETCH statement sets FOUND true if it returns a row, false if no row is returned.

A MOVE statement sets FOUND true if it successfully repositions the cursor, false otherwise.

A FOR or FOREACH statement sets FOUND true if it iterates one or more times, else false. FOUND is set this way when the loop exits; inside the execution of the loop, FOUND is not modified by the loop statement, although it might be changed by the execution of other statements within the loop body.

RETURN QUERY and RETURN QUERY EXECUTE statements set FOUND true if the query returns at least one row, false if no row is returned.

Other PL/pgSQL statements do not change the state of FOUND. Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

FOUND is a local variable within each PL/pgSQL function; any changes to it affect only the current function.

alexk
  • 361
  • 1
  • 3