pgcurlWeb-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:

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

urlget() expects a single argumenta 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 argumentsthe 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).

Категории