pgcurl—Web-enabling Your PostgreSQL Server

The pgcurl package (gborg.postgresql.org/project/pgcurl) adds networking features to a PostgreSQL database. pgcurl wraps the libcurl networking library in a set of five server functions:

• urlencode( TEXT ) RETURNS TEXT

  urlencode() translates “special” characters into their URL-escaped form. Specifically, urlencode() converts any character outside of the ranges a–z, A–Z, and 0–9 into the form %nn (where nn is the two-digit hexadecimal representation of the character according to RFC 2396).

• urldecode( TEXT ) RETURNS TEXT

  urldecode() translates an URL-escaped string into a plain-text form. Specifically, urldecode() any sequence of the form %nn (where nn is the two-digit hexadecimal representation of a character) into the corresponding character.

• urlget( TEXT ) RETURNS TEXT

  urlget() downloads a resource (and HTML web page, a file, or whatever) from a web or FTP server and returns the resource as a TEXT value.

• urlpost( TEXT, TEXT ) RETURNS TEXT

  urlpost() sends an HTTP POST request (including URL-encoded arguments) to a web server and returns the response as a TEXT value.

• urlhead( TEXT ) RETURNS TEXT

  urlhead() is similar to urlget() except that urlhead() returns the meta-data associated with a resource (Content-Type, Expires, Transfer-Encoding, and such).

The most interesting functions are urlget() and urlpost().

urlget() expects a single argument—a string that specifies a URL that you want to download. When you call urlget(), it downloads the resource that you specify. A typical call to urlget() might look something like this:

movies=# INSERT INTO news( source, html ) VALUES (
movies(#   'npr',
movies(#   urlget( 'http://www.npr.org/index.html' ));
INSERT 985949 1

urlget() can handle simple HTTP (http://), secure HTTP (https://), simple FTP (ftp://), secure FTP (ftps://), file access (file://), even somewhat obscure protocols such as gopher, telnet, dict, and ldap. If urlget() succeeds, it returns the entire resource as a single TEXT value. Needless to say, urlget() may take a while to complete if you are downloading a large document (or have a slow network connection).

The urlpost() function lets you send an HTTP POST request to a web server. urlpost() expects two arguments—the first argument is the URL that you want to post to and the second argument contains the data that you want to post. For example, say that you are hosting a web site that displays information about the “Movie Of The Week” featured at your video store. You could update the featured title like this:

movies=# SELECT urlpost( 'http://virtualVid.example.com/motw.cgi',
movies(#   'name=' || urlencode( title ) ||
movies(#   '&date=' || urlencode( current_date ))
movies-# FROM tapes WHERE tape_id = 'AB-67472';

That might be a little difficult to read, so here's a closer look at the call to urlpost():

urlpost( 'http://virtualVid.example.com/motw.cgi',
    'name=' || urlencode( title ) '&date=' || urlencode( current_date ))

I've used urlencode() to translate special characters (such as spaces and dashes) into URL-happy form. PostgreSQL assembles the second argument into a single string that looks like this:

'name=The%20Godfather&date=2005%2D04%2D16'

The net effect is the same as browsing to the following URL in a web browser:

http://virtualVid.example.com/motw.cgi?name=The%20Godfather&date=2005%2D04%2D16

urlpost() returns the HTML page produced by the web server as a single TEXT value (just like urlget() does).