Adding log10() to PostgreSQL

I’m pretty excited because I’m writing a patch that will add functions to PostgreSQL, so I’m hacking into Postgres.

The first function that I’d like to add is the log10 one. It’s needed for SQL:2016 compliance. PostgreSQL already implemented logarithm in base 10 (it’s called log), so I just need to add an alias to point to the function that was implemented in Postgres.

What to change 🔗

I first decided to take a tour of every file I’ll need to change and I came up with:

• doc/src/sgml/func.sgml
• src/include/catalog/pg_proc.dat

I decided not to change the regression tests as it’s only an alias. If log is good, log10 will be.

Let’s go 🔗

Documentation 🔗

First, I changed the documentation. That way, I’ll be sure of what I wanted to do. It helped me understand I’ll have 2 functions: one that will take a number and another one that will take a double precision.

I decided to add that new log10 function in a new row just below the log() function.

      <row>
       <entry>
        <indexterm>
         <primary>log10</primary>
        </indexterm>
        <literal><function>log10(<type>dp</type> or <type>numeric</type>)</function></literal>
       </entry>
       <entry>(same as input)</entry>
       <entry>base 10 logarithm</entry>
       <entry><literal>log10(100.0)</literal></entry>
       <entry><literal>2</literal></entry>
      </row>

And that was done for documentation.

Code 🔗

Now, I can add the function to Postgres. The file src/include/catalog/pg_proc.dat just stores data for the future catalog of Postgres functions.

I needed unused oids to be able to add my function to the Postgres catalog. Call me naive, I thought items were added by ascending order of oid so I scanned the file looking for the next free oid after my two log functions and added my 2 functions.

That’s it! Patch done! Time to compile!

Duplicate OIDs detected:
1352
1747
found 2 duplicate OID(s) in catalog data

What? Ok, functions are not stored in ascending order of oid in that file. And sure enough, those oids were already taken. So how could I find which ones are free? I searched for a readme somewhere. Nope, there was nothing.

At the last moment, when I was becoming desperate, I did a simple ls on the directory. And then a bright green file (green means executable in my shell) ringed a bell in my mind. That beautiful file was called `unused_oid.

I executed that file and found out the next oids I was looking for were 1364 and 2023.

I could write my patch!

{ oid => '1364', descr => 'base 10 logarithm',
  proname => 'log10', prorettype => 'float8', proargtypes => 'float8',
  prosrc => 'dlog10' },

{ oid => '2023', descr => 'base 10 logarithm',
  proname => 'log10', prolang => '14', prorettype => 'numeric',
  proargtypes => 'numeric', prosrc => 'select pg_catalog.log(10, $1)' },