PostgreSQL exception handling on select statements with calculations

Question

I'm having issues with the exception handling of postgresql, and I'm looking for some tips/advice how to tackle this. Say I want to do the following:

SELECT col1 / col2
FROM table

The first problem that arises is that if at some point a value in col2 = 0, the query throws an exception. A solution to this is to add a NULLIF() in the denominator.

In our case, users can make their own formulas, which have to be parsed by the database, so we do not have the knowledge about the division in advance. We could make a fancy formula parser that adds NULLIF() in the right places, but then don't get me started on taking square root of negative numbers..

So I'm wondering if there is a better solution to the problem. Does something like this exist?

SELECT col1 / col2
  exception
   return null
FROM table

Or do I need to make use of the 'function' feature of postgresql? Is it possible to combine two columns like this?

CREATE OR REPLACE FUNCTION somefunction(col1, col2) RETURNS real AS $$
BEGIN
  RETURN col1 / col2;
    exception when division_by_zero then
        return null;
END;
$$ LANGUAGE plpgsql;

SELECT somefunction(col1, col2, ..)
FROM table;

So keep in mind that we do not know the formula in advance! Thanks!

If you give your users the permission to directly write SQL expressions, you also give them the responsibility to write proper valid SQL and add the nullif() calls themselves. Surely you already set up all the relevant safeguards to limit sql injections? Catching exceptions in there should be easy. If no, then yeah you definitely should use a fancy formula parser! — Bergi
– Bergi, Commented Mar 5, 2021 at 1:25
Hi Bergi, thanks for your answer. We have some safeties in place, but in general it's not the nicest thing to do of course. Could you elaborate on 'Catching exceptions in there should be easy'? — Matthias Baert
– Matthias Baert, Commented Mar 5, 2021 at 9:11

Laurenz Albe · Accepted Answer · 2021-03-05 03:38:33Z

1

In PostgreSQL you can define your own operators. Given your function, you could

CREATE OPERATOR public./ (
   LEFTARG = real,
   RIGHTARG = real
   FUNCTION = public.somefunction
);

SET search_path = public, pg_catalog;

This would use your custom function for every division.

Allowing your users to run SQL statements is a bad idea. SQL is so powerful that they can do anything - they can change the search_path, and they can easily bring your database server to its knees.

answered Mar 5, 2021 at 3:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthias Baert Over a year ago

Hi! Many thanks for your answer. Completely agree that user generated SQL commands is a bad idea. This is for internal use, so we can get away with shady stuff.

Collectives™ on Stack Overflow

PostgreSQL exception handling on select statements with calculations

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related