Invariant Properties

  • rss
  • Home

Creating User-Defined C Functions in PostgreSQL

Bear Giles | December 25, 2014

Many experienced database developers are familiar with user-defined functions implemented in SQL or a procedural language. PostgreSQL itself supports PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, and has third-party support for PL/sh, PL/Java, PL/Ruby, PL/PHP and even PL/R.

Recent versions of PostgreSQL also support SQL/MED (foreign data) with foreign data wrappers (FDW). This allows the database user to access foreign data sources – other RDMSes such as Oracle or MySQL, noSQL databases, even services such as twitter, as though they were PostgreSQL tables. Obviously there can be performance issues but if an architect only needs to perform a simple task, e.g., sending a tweet when certain conditions are met, it may be easier to use a FDW and database query than to incorporate native twitter support.

Sometimes this is not enough. We may wish to add support for a new procedural language or foreign data wrapper. We may wish to perform work involving external libraries on the database server instead of the application server for efficiency. We may wish to hide implementation details from the database user.

A subset of the last item is management of cryptographic material. Most webapps manage the cryptographic material themselves but proper key management is difficult. For instance encryption keys should be rotated on a regular basis but many webapps have never changed their database keys because there is no provision for it. Their only option is to shut down the application, run an application that loads every record, decrypts it, reencrypts it, and then updates each record. Key management on the database side is still a difficult problem but the solutions are far more likely to be reusable.

PostgreSQL Extensions

It is possible to create user-defined functions in a sql script:

  1. CREATE OR REPLACE FUNCTION dgst_sha1(text)
  2. RETURNS text
  3. AS 'pgopenssltypes', 'dgst_sha1'
  4. LANGUAGE C IMMUTABLE STRICT
CREATE OR REPLACE FUNCTION dgst_sha1(text)
RETURNS text
AS 'pgopenssltypes', 'dgst_sha1'
LANGUAGE C IMMUTABLE STRICT

and provide the implementation by dropping the appropriate shared library in the $libdir directory. See C-Language Functions for details, or my own Introduction to PL/Java for examples of the SQL definitions for functions, triggers, operators, and indexes.

This is unmaintainable for anything other than the smallest tasks. We want to bundle everything we need into one object that we can load and unload it in a single operation. This ensures there are no oversights or inconsistencies. PostgreSQL already has a solution to this problem: PostgreSQL Extensions.

PostgreSQL Extension Network (PGXN)

Writing a PostgreSQL extension from scratch can be tricky. A better approach is to create a skeleton using the PGXN utils package and then install it with the pgxn-client utility from the PostgreSQL Extension Network.

Source Code

I am not including any source code since there are numerous projects listed at the PGXN site. For instance semver (semantic version data type, git or pgaudit. As always all public code should be viewed critically – you don’t want to learn the wrong lessons by reading bad code. (Do not take my reference to these projects as an endorsement.)

Note: if you want to use a shared library your Makefile should include the libraries in a SHLIB_LINK declaration. The system will automatically pull in any dependencies when the module is loaded.

See Also

PostgreSQL Functions By Example (slide deck)

HowTo – Create PostgreSQL C library functions

Comments
No Comments »
Categories
PostgreSQL, server programming, stored procedures, Uncategorized
Comments rss Comments rss
Trackback Trackback

Archives

  • May 2020 (1)
  • March 2019 (1)
  • August 2018 (1)
  • May 2018 (1)
  • February 2018 (1)
  • November 2017 (4)
  • January 2017 (3)
  • June 2016 (1)
  • May 2016 (1)
  • April 2016 (2)
  • March 2016 (1)
  • February 2016 (3)
  • January 2016 (6)
  • December 2015 (2)
  • November 2015 (3)
  • October 2015 (2)
  • August 2015 (4)
  • July 2015 (2)
  • June 2015 (2)
  • January 2015 (1)
  • December 2014 (6)
  • October 2014 (1)
  • September 2014 (2)
  • August 2014 (1)
  • July 2014 (1)
  • June 2014 (2)
  • May 2014 (2)
  • April 2014 (1)
  • March 2014 (1)
  • February 2014 (3)
  • January 2014 (6)
  • December 2013 (13)
  • November 2013 (6)
  • October 2013 (3)
  • September 2013 (2)
  • August 2013 (5)
  • June 2013 (1)
  • May 2013 (2)
  • March 2013 (1)
  • November 2012 (1)
  • October 2012 (3)
  • September 2012 (2)
  • May 2012 (6)
  • January 2012 (2)
  • December 2011 (12)
  • July 2011 (1)
  • June 2011 (2)
  • May 2011 (5)
  • April 2011 (6)
  • March 2011 (4)
  • February 2011 (3)
  • October 2010 (6)
  • September 2010 (8)

Recent Posts

  • 8-bit Breadboard Computer: Good Encapsulation!
  • Where are all the posts?
  • Better Ad Blocking Through Pi-Hole and Local Caching
  • The difference between APIs and SPIs
  • Hadoop: User Impersonation with Kerberos Authentication

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Pages

  • About Me
  • Notebook: Common XML Tasks
  • Notebook: Database/Webapp Security
  • Notebook: Development Tips

Syndication

Java Code Geeks

Know Your Rights

Support Bloggers' Rights
Demand Your dotRIGHTS

Security

  • Dark Reading
  • Krebs On Security Krebs On Security
  • Naked Security Naked Security
  • Schneier on Security Schneier on Security
  • TaoSecurity TaoSecurity

Politics

  • ACLU ACLU
  • EFF EFF

News

  • Ars technica Ars technica
  • Kevin Drum at Mother Jones Kevin Drum at Mother Jones
  • Raw Story Raw Story
  • Tech Dirt Tech Dirt
  • Vice Vice

Spam Blocked

53,793 spam blocked by Akismet
rss Comments rss valid xhtml 1.1 design by jide powered by Wordpress get firefox