Monday, March 28, 2011

MySQL div

I have to port one benchmark from MySQL. Test wasn't success because there was a div by zero. A result in MySQL is NULL, PostgreSQL raises a exception. I created new operator, that simulates MySQL's behave:
postgres=# CREATE OR REPLACE FUNCTION my_div(double precision, double precision) 
           RETURNS double precision AS $$ 
             SELECT CASE WHEN $2 = 0 
                              THEN NULL::double precision 
                              ELSE $1::double precision / $2::double precision END; 
           $$ LANGUAGE sql;
CREATE FUNCTION
postgres=# select my_div(2,3);
      my_div       
-------------------
 0.666666666666667
(1 row)

postgres=# select my_div(2,0);
 my_div 
--------
       
(1 row)

postgres=# CREATE OPERATOR // (PROCEDURE = my_div, LEFTARG = double precision, RIGHTARG= double precision);
CREATE OPERATOR
postgres=# SELECT 10//3;
     ?column?     
------------------
 3.33333333333333
(1 row)

postgres=# SELECT 10//0;
 ?column? 
----------
         
(1 row)

Sunday, March 27, 2011

simple function for test if somebody is superuser

I needed a small functions:
CREATE OR REPLACE FUNCTION is_superuser(text)
RETURNS boolean AS $$
  SELECT EXISTS(SELECT 1 FROM pg_roles WHERE rolname = $1 AND rolsuper);
$$ LANGUAGE sql;

postgres=# SELECT is_superuser('tom');
 is_superuser 
--------------
 f
(1 row)