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
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
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
--#GIVEN A users search criteria - convert to TSearch Suitable format
--#Return results as XML to be consumed by REST client;
var_tsearch_query := REPLACE(REPLACE(REPLACE(array_to_string(ARRAY(SELECT lower(foo)
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))
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'))
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
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).
This is a continuation of our REST series. The following topics have already been covered Showcasing REST in PostgreSQL - The PreQuel we went over what REST is and isn't REST in PostgreSQL Part 1 - The DB components we loaded the Pagila database and
Tracked: Apr 09, 22:29
In prior articles of this series, we covered the following: Showcasing REST in PostgreSQL - The PreQuel we went over what REST is and isn't REST in PostgreSQL Part 1 - The DB components we loaded the Pagila database and created a db plpgsql search func
Tracked: May 07, 15:46
Tracked: Jul 12, 04:46
Tracked: Jul 14, 03:59