REST in PostgreSQL Part 1 - The DB components

In this section we'll go over creating the views and stored functions that our REST Server service will rely on.

Our REST service will be a thin wrapper around a pgsql function that accepts film queries and returns results in XML.

Loading the database

Step 1: Download the Pagila 0.10.0 database from http://pgfoundry.org/frs/?group_id=1000150&release_id=570 and load the Pagila database using the following commands:

Note:For windows users - psql is usually located at "C:\Program Files\PostgreSQL\8.3\bin\psql"

psql -h localhost -p 5433 -U postgres -c "CREATE DATABASE pagila ENCODING 'UTF8'"
psql -h localhost -p 5433 -U postgres -c "CREATE ROLE pagila_app LOGIN PASSWORD 'pg@123'"
psql -h localhost -p 5433 -U postgres -d pagila -f "pagila-schema.sql"
psql -h localhost -p 5433 -U postgres -d pagila -f "pagila-data.sql"

Writing our view

We are creating a view to make querying the data easier, but we don't need to give our pagila_app account rights to the view since all access will be thru our stored function. The view is mostly there to provide a namespace for the XML output.

CREATE OR REPLACE VIEW vwfilms AS 
 SELECT film.film_id AS fid, film.title, film.description, category.name AS category, 
    film.rental_rate AS price, film.length, film.rating, film.fulltext
   FROM category
   LEFT JOIN film_category 
    ON category.category_id = film_category.category_id
   LEFT JOIN film 
    ON film_category.film_id = film.film_id

Writing our search stored function

Now we shall write a stored function that takes a search criteria and returns an XML result. Note that we are designating the function is accessed with SECURITY DEFINER which means the executer of the function has all rights to items used within the function as long as the creator of the function has rights. Keep in mind we didn't really think long and hard about how to do this efficiently so our implementation of converting to Tsearch format is probably highly inefficient and is a brain dead implementation of converting a simple search phrase into suitable Tsearch format. Feel free to comment as to a better way of doing this or bash us for our stupid implementation. We'll try hard not to be offended.

This is shown below:

CREATE OR REPLACE FUNCTION fnget_film_search_results(arg_search character varying, 
    arg_num_results integer, arg_start_at integer)
  RETURNS xml AS
$BODY$
       --#GIVEN A users search criteria - convert to TSearch Suitable format
       --#Return results as XML to be consumed by REST client;
DECLARE
    var_sql text;
    var_count integer;
    var_count_xml xml;
    var_tsearch_query text;
BEGIN
    var_tsearch_query := REPLACE(REPLACE(REPLACE(array_to_string(ARRAY(SELECT lower(foo)
        FROM
            regexp_split_to_table(arg_search, E'\\s+') As foo
            WHERE foo SIMILAR TO E'\\w+'), '&'),  '|and', '&'), '|or', '|') , 'not&', '!');
    RAISE NOTICE '"%"', var_tsearch_query;
    SELECT count(fid) INTO var_count FROM vwfilms WHERE fulltext @@ to_tsquery(var_tsearch_query);
    var_count_xml := '<?xml version="1.1"?><resultsummary><count>' || CAST(var_count As varchar(20)) 
            || '</count></resultsummary>';
    var_sql := 'SELECT fid, title, description, category, price, length, rating 
        FROM vwfilms WHERE fulltext @@ to_tsquery(' || quote_literal(var_tsearch_query) || ')';
    var_sql := var_sql || ' LIMIT ' || CAST(arg_num_results As varchar(20)) 
        || ' OFFSET ' || CAST(arg_start_at as varchar(20));
    RETURN '<results>' || xmlconcat(var_count_xml, query_to_xml(var_sql, false, false, 'vwfilms')) 
        || '</results>';
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100;
GRANT EXECUTE ON FUNCTION fnget_film_search_results(character varying, integer, integer) TO pagila_app;

To test out our function we do this:

SELECT fnget_film_search_results('not epic but has dogs',10,1);
SELECT fnget_film_search_results('not epic but has dogs',10,20);

In the next issue we will cover consuming this via a .NET REST Service. We will show both C# and VB.NET implementations which are compatible with Microsoft.NET ASP.NET 2.0 as well as Mono.NET 1.2.6 (C# and Mono Basic).