pgcurlWeb-enabling Your PostgreSQL Server

The pgcurl package ( 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 az, AZ, and 09 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 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( '' ));
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( '',
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( '',
 '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:


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

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


Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL


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


Replicating PostgreSQL Data with Slony

Contributed Modules


PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
Year: 2004
Pages: 261 © 2008-2020.
If you may any questions please contact us: