I have always wanted to know how ./configure does its magic. To a Gnu newbie the Gnu Build System seems utterly complex. Available manuals for Autoconf, Automake and Libtool are several hundred pages of difficult-to-read text. Learning curve is steep. Way too steep to produce a simple script!
But then -last week as I googled around- I found these gems:
- Using Automake and Autoconf with C++
- Using C/C++ libraries with Automake and Autoconf
- Building C/C++ libraries with Automake and Autoconf
These make understanding basic path of execution much easier and are an excellent pre-read before feeding your brain with official manuals.
Coming from Windows background I was a bit worried about the complexity of accessing Postgres databases from C++. I'm still having nightmares of early ODBC programming back in the 90ties. But as a mature developer I quickly overcame Microsoft's fixation on building the mother of all DBMS access technologies; that is - a common database mechanism to access various DBMSes.
Seriously, how many times in life have you ported a database from MS SQL Server to Oracle? There is a brilliang library for accessing Postgres (and only Postgres!) from C++ available. It is pqxx. It comes with a tutorial to help you getting started.
Here is Hello World of libpqxx. It doesn't get any simpler then that. Following code executes a query on a database.
#include <pqxx/pqxx>
using namespace pqxx;
void execute_query() {
connection cn("dbname=my_database");
work w(cn, "mytransaction");
w.exec("INSERT INTO city(city_name) VALUES ('Ljubljana');");
w.commit();
}
And now for something a bit more complex. Today we're going to discover the art of creating a stored procedure / function layer in Postgres database.
Let us first agree on terminology. In Postgres there is no difference between a stored procedure and a function. A procedure is merely a function returning void. Thus from now on we will only use term function.
Just in case you wonder - as a mature DBMS Postgres prepares (precompiles) all functions for optimal performance, exactly as its commercial competitors.In contemporary databases functions are commonly used to implement security layer. It is generally easier to give a user permission to execute a function that manipulates many database tables to complete a business operation then it is to assign him or her just the right permissions on all involved database tables for the same result.
Therefore modern designs introduce an additional layer of abstraction to access database tables. This layer is implemented via functions. In such scenario users can access database tables only through functions. They have no direct access to database tables.
To implement this design we need to know more about the Security of definer concept.
Security of definer
In Postgres a function can be defined to have “Security of definer” property set. You can set this propety in pgAdmin (see the screenshot bellow).
To set this property manually add SECURITY DEFINER to the end of function definition like this -
CREATE FUNCTION insert_city(character varying)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO city("name") VALUES($1);
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
Adding this to a function will make it run under the context of the owner of the function instead of context of the caller of the function. This allows a user to insert city into a table without having insert permission on it. As long as he has permission to execute this function and the owner of the function has permission to insert into city table. It is a way to implement chained security on Postgres.
Postgres functions that return datasets
Postgres functions that return data sets are poorly documented and their syntax might look a bit awkward to those of you used to SQL Server. When a function in Postgres returns multiple results it must be declared to return SETOF some type. Call syntax for such functions is a bit different then what you are used to. You call it using SELECT * FROM function() instead of usual SELECT function() call.
SELECT function_returning_setof(); -- Wrong!
SELECT * FROM function_returning_setop(); -- OK!
SELECT function_returning_scalar(); -- OK
Let's imagine we have a table of all cities with two fields – city_id of type SERIAL, and name of type VARCHAR(80). Actually we're imagining this througout this article.
Here is a code fragment showing how to write a function returning all cities.
CREATE FUNCTION list_all_cities()
RETURNS SETOF city AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN (SELECT * FROM city) LOOP
RETURN NEXT rec;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Call the function.
SELECT * FROM list_all_cities();
This code is using generic type called RECORD. Try experimenting with %ROWTYPE to obtain same results. Function above returns type SETOF city - each record in the set has the same structure as a record of city table. But what if you wanted to return another structure? One way is to create a type like this -
CREATE TYPE list_all_city_names_result_type AS (city_name varchar(80));
CREATE FUNCTION list_all_city_names()
RETURNS SETOF list_all_city_names_result_type AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN (SELECT "name" FROM city) LOOP
RETURN NEXT rec;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM list_all_city_names();
If this imposes too harsh limitations then you can also use the generic RECORD data type as return type and tell the procedure what to expect when calling it.
CREATE FUNCTION list_all_city_names2()
RETURNS SETOF RECORD AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN (SELECT "name" FROM city) LOOP
RETURN NEXT rec;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM list_all_city_names2()
AS ("city_name" varchar(80)); -- Expect varchar(80)
There's one more trick. I don't recommend it but I'm publishing it anyways. If you'd like to make things really simple for you then you could use another language instead of plpgsql. For example, using sql code you can write queries directly into your function like this -
CREATE FUNCTION getallzipcodes()
RETURNS SETOF zip AS
$BODY$
SELECT * FROM zip;
$BODY$
LANGUAGE 'sql';
Stored procedure layer and automatic code generation
When I create stored procedure layer I like to generate code for basic CRUD functions. I only write code for complex business functions by hand. Thus in my next article I am going to write code generator to do just that for Postgres/c++ pair.
But right now let's just explain some concepts. CRUD functions are - Create (new record from data), Read (given ID), Update (existing record given ID and new data) and Delete (given id) for single table. These functions are needed for every table. For example for table city you would have following functions:
- create_city(name),
- read_city(id),
- update_city(id,new_name),
- delete_city(id).
Two more types of functions are often needed. Get functions and list functions (sometimes called find functions). The difference between get and list function is that a get function always return single result. For example get_city_id_by_name(name) or get_user_by_phone(phone). Whereas list function returns all records that match the condition provided as parameters. For example - list_cities_starting_with(word) or list_persons_older_then(age) or list_all_countries().
Some people prefer list_ and other prefer find_ prefix for list functions. It is a matter of taste; as long as your programming stlye is consistent.
Table above shows functions that will be needed for almost any table and are therefore candidates for automatic code generation. We'll deal with code generation in one of our following articles.
If you would like to publish SQL or C++ code on your blog hosted by Blogger I recommend SyntaxHighlighter.
You can install the SyntaxHighlighter Blogger widget somewhere on your blog. I installed it at the bottom of this blog.
Use tag pre to publish your code. For example:
<pre name="code" class="SQL">
-- Sample
SELECT * FROM city;
</pre>
will produce this result
-- Sample
SELECT * FROM city;
Supported languages include c++, sql, c#, etc. All supported language tags can be found here.