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() translates "special" characters into their URL-escaped form. Specifically, urlencode() converts any character outside of the ranges az, AZ, and 09 into the form %nn (where nn is the two-digit hexadecimal representation of the character according to RFC 2396).
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() 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() sends an HTTP POST request (including URL-encoded arguments) to a web server and returns the response as a TEXT value.
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 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).
Part I: General PostgreSQL Use
Introduction to PostgreSQL and SQL
Working with Data in PostgreSQL
PostgreSQL SQL Syntax and Use
Performance
Part II: Programming with PostgreSQL
Introduction to PostgreSQL Programming
Extending PostgreSQL
PL/pgSQL
The PostgreSQL C APIlibpq
A Simpler C APIlibpgeasy
The New PostgreSQL C++ APIlibpqxx
Embedding SQL Commands in C Programsecpg
Using PostgreSQL from an ODBC Client Application
Using PostgreSQL from a Java Client Application
Using PostgreSQL with Perl
Using PostgreSQL with PHP
Using PostgreSQL with Tcl and Tcl/Tk
Using PostgreSQL with Python
Npgsql: The .NET Data Provider
Other Useful Programming Tools
Part III: PostgreSQL Administration
Introduction to PostgreSQL Administration
PostgreSQL Administration
Internationalization and Localization
Security
Replicating PostgreSQL Data with Slony
Contributed Modules
Index