These notes condense some of what I tried to express during two Tech Salons on PostgreSQL in 2010. The lucky guinea pigs who were there will hopefully enjoy this document as a review and I promise some interesting new material. I hope that this document will also assist anyone wanting to join any future projects in which PostgreSQL is a key tool.
Nearly every serious software project needs a database system. Rather than trying to recap the reasons why we see PostgreSQL as the best choice I'll just reference the PostgreSQL website and encourage everyone to browse it and then ask questions.
Once you decide to give PostgreSQL a chance, download and install it on some convenient computer. Find the PostgreSQL tutorial and spend some time working through the SQL examples. If you're a self-starter you could do this on your own; alternatively you might arrange to do this with a partner, perhaps someone who has already done some of this, using the "each one teach one" model.
Once you've gone through the tutorials, I recommend the following project:
Create a schema (for now we'll think of a schema as a set of table and view
definitions) which describe your life. It should contain several primary
tables connected by linkage tables. One of the tables should be
persons which should have a row for a number of diverse important people
in your life. One of the rows should represent you! Some other possible
entity tables might be
You'll also want tables and/or enumerations to express types of people, groups, etc. as well as other properties. You should ensure referential integrity with primary keys and foreign key references.
Make the project fun: Avoid doing too much that is similar or tedious. Look for how to represent diverse and interesting features of your world. Share what you've come up with with others in the Tech Group.
You can work with PostgreSQL using any of several graphical interfaces, or with the psql client in a terminal window. I've found that serious work becomes much easier if you bite the bullet and learn Emacs. Emacs will let you run one-or-more database sessions in windows and quickly move definitions and results between sql textfiles, documents, web pages and one or more databases.
You can access PostgreSQL from applications written in any programming language. You can do server-side PostgreSQL programming in more than a dozen programming languages. Three are built-in and worth special mention:
In the second Tech Salon on PostgreSQL we looked at some examples of metaprogramming. For example, in the Wicci System the single meta-function call
replaces around 200 lines of regular code which includes
Then the single meta-function call
creates an object oriented class in which the rows of the
SELECT create_type_class('doc_node_refs', 'doc_node_tree_rows');
TABLE doc_node_tree_rowsserve as instances.
Finally, meta-function calls like
bind specific SQL functions to the operations of the new class.SELECT type_class_op_method( 'doc_node_refs', 'doc_node_tree_rows', -- the type and table of the class 'ref_env_crefs_to_text_op(refs, env_refs, crefs)', -- the C language operation 'tree_doc_to_text(doc_refs, env_refs, crefs)' -- the SQL language method );
We're using metaprogramming to build support for the object-oriented programming paradigm, with full static type-checking, into PostgreSQL which out-of-the-box only supports the relational and procedural programming paradigms. Once you learn these three meta-functions you're able to design at a higher level, write much less code and get much better type-checking without sacrificing efficiency.
Where and how to use metaprogramming is a serious design issue. We saw a simple example of metaprogramming where after an abstract base table (the parent table of an inheritance hierarchy) was declared we used the meta-function call
to attach a trigger to the table which will throw an exception should we ever try to insert any rows in that table.
SELECT declare_abstract('table name');
An alternative might be a meta-function
which would go through all tables in the given schema whose names start with
SELECT declare_abstracts_in('schema name');
abstract_and automatically call
declare_abstracton each one. This would create an auto-magic feature where the naming convention of the prefix
abstract_produces a semantic result. At this time I have chosen not to do this.
But consider the situation where we frequently have pairs of SQL functions which are defined like this:
How might we reduce the boilerplate with some metaprogramming?CREATE OR REPLACE FUNCTION try_schema_trim(text) RETURNS text AS $$ SELECT lower(regexp_replace($1, '[[:space:]]', '', 'g')) $$ LANGUAGE sql STRICT; CREATE OR REPLACE FUNCTION schema_trim(text) RETURNS text AS $$ SELECT non_null(try_schema_trim($1), 'schema_trim(text)') $$ LANGUAGE sql; COMMENT ON FUNCTION schema_trim(text) IS 'Normalize a comma-separated list of schemas'; SELECT module_provides_function('schema_trim(text)');
One way would be to create a meta-function which creates the second function given the signature of the first one. It could also handle the commenting and the registration of both functions as belonging to the current module. The new code would look like this:
Does this seem a bit nicer?CREATE OR REPLACE FUNCTION try_schema_trim(text) RETURNS text AS $$ SELECT lower(regexp_replace($1, '[[:space:]]', '', 'g')) $$ LANGUAGE sql STRICT; SELECT create_non_null_function_for( 'try_schema_trim(text)', 'Normalize a comma-separated list of schemas' );
We could go whole hog and create all sql functions through meta-functions. We could create single functions like this:
but then it becomes easy to create a meta-function for just such pairs as these and the code reduces to:SELECT create_strict_sql_function( 'try_schema_trim', -- name of function to create 'Try to normalize a comma-separated list of schemas', $$ SELECT lower(regexp_replace($1, '[[:space:]]', '', 'g')) $$, 'text', 'text' -- return and argument type(s) ); SELECT create_sql_function( 'schema_trim', -- name of function to create 'Normalize a comma-separated list of schemas', $$ SELECT non_null(try_schema_trim($1), 'schema_trim(text)') $$, 'text', 'text' -- return and argument type(s) );
with the result being the creation of both functions, both with comments and both registered with the module in which this code appears.SELECT create_strict_and_non_null_sql_function_pair( 'schema_trim', -- for both try_schema_trim and schema_trim 'Normalize a comma-separated list of schemas', $$ SELECT lower(regexp_replace($1, '[[:space:]]', '', 'g')) $$, 'text', 'text' -- return and argument type(s) );
Metaprogramming using PostgreSQL meta-functions can be defined to automate and enforce any design or implemetation patterns. However, they then require that anyone working with that code learn what those meta-functions do. If you want to make it easy for anyone who knows standard SQL to read and understand your code you should use meta-functions very sparingly. I'm still defining most of my functions using standard SQL commands rather than calling meta-functions. It is only when repetitious code starts getting hard to maintain that I will stop and define one or more meta-functions to abstract away the bloat.
These examples only scratch the surface of what is possible with meta-programming in PostgreSQL. I hope that you find the possibilities exciting!
Use the handy Pu'uhonua Contact Page
Return to Touch Pu'uhonua's Writings Return to Touch Pu'uhonua