Over my short hiatus from General Bits, I've been working on the infrastructure of my business, Varlena, LLC. I have always offered PostgreSQL consulting. Now we've begun offering technical support and I also hope soon to offer training very soon. For more information on Varlena, LLC and our offerings feel free to contact me directly at elein@varlena.com.

As I begin the second year of publishing PostgreSQL General Bits, the information will expand. A wider and deeper script repository is under way which will support PostgreSQL information and Advocacy. I would like to make a call to all authors and technical people to invite you to submit more scripts and articles for inclusion in General Bits. A broader community voice is entirely appropriate and very welcome. If you are concerned about your writing or English skills, editing help is certainly available.

And as always, comments and questions are always welcome.

Editor: elein at varlena.com

Domain Basics [GENERAL] Domains (Was [PERFORM] Views With Unions) 01-Aug-2003

A domain is a way to name a default and a set of constraints on a a data type without changing the datatype. The primary purpose of a domain is to consolidate constraint logic for a set of fields which have the same constraints and/or formats. It is usually used to verify formats of well known types of column values.

For example, email addresses are text fields which must be non null and in name@domain format where name is alphanumeric and domain is alphanumeric with a valid trailing, e.g. .com, .org, .net, etc. To check this format, write a function which returns a boolean to determine if the address is in its proper format. (The function is best written in plperl and is left as an exercise to the reader--send in your best effort for future publication :-)

Usually, one would then define a table using email as follows.

	create table addressbook (
		...
		email	text NOT NULL CHECK ( emailformatok( email ) ),
		...
	); 

	create domain email_d AS text DEFAULT 'none'
	   CONSTRAINT email_domain check( emailcheckok( VALUE) );
	create table addressbook (
	   ...
	   email email_d,
	   ...
	); 

Domains are not User Defined Types, however, in practice they are cousins. Domains inherit all of the casts and functions of the underlying data type. For example, if you define a domain based on an integer, you can still do comparisons and order by on the domain as if it were an integer. So a domain is an inherited data type as opposed to a newly created user defined type.

You can define functions on domains, but there is a bug/caveat. If you declare your function to return a domain type, the check is not executed on return. You are able to return values that do not meet the domain's constraints. (Question pending in pgsql-general.)

Another reason to use domains is that you can ALTER them. You can change the check constraint as necessary in one place. You cannot, however, alter the underlying data type of the domain.

In the Illustra and Informix ORDBMS, these domains were defined as CREATE TYPE bar UNDER TYPE foo; And Jim Melton warns that domains created in the PostgreSQL style may be deprecated in future SQL standards. I expect the standard will lean toward create types UNDER types.

Contributors: elein at varlena.com Francisco J Reyes fran at natserv.net, Christopher Browne cbbrowne at libertyrms.info, Stephan Szabo sszabo at megazone.bigpanda.com, Ron Johnson ron.l.johnson at cox.net, Jochem van Dieten jochemd at oli.tudelft.nl, Tom Lane tgl at sss.pgh.pa.us

Empty Arrays [GENERAL] Declaring empty, non-NULL array in plpgsql 23-Nov-2003

An empty array literal is quoted curly brackets: '{}' With an empty array declared correctly in SQL or plpgsql (for example) you can then append to the array as you like.

In this example, the varchar array is declared as empty and then is appended to with each number from 1 to the number passed into the routine. Notice this nice syntax of the FOR LOOP and that a simple concatenation function can be used to add array elements at the end of the array.

	regression=# create function foo(int) returns varchar[] as '
	regression'# declare
	regression'#   arr varchar[] := ''{}'';
	regression'# begin
	regression'#   for i in 1..$1 loop
	regression'#     arr = arr || i::varchar;
	regression'#   end loop;
	regression'# return arr;
	regression'# end' language plpgsql;
	CREATE FUNCTION
	regression=# select foo(10);
          	foo
	------------------------
 	{1,2,3,4,5,6,7,8,9,10}
	(1 row)

With 7.4 available, there are many more things you can do with arrays easily. Some of these were previously covered in General Bits, and more examples will be available.

Contributors: CSN cool_screen_name90001 at yahoo.com, Joe Conway mail at joeconway.com, Tom Lane tgl at sss.pgh.pa.us

Syntax for Indexing Array Elements [GENERAL] Index on array element 24-Nov-2003

It is possible to index on a specific array element in both 7.3 and 7.4. In 7.3 one would use a simple functional index and pass to it the array. This is a little clunky. In 7.4 you can index on the element(s) directly or in an expression.

In 7.3, for example, create a function which returns the first element of the array:

	create function first(text[]) 
	returns text 
	as 'select $1 [1]'
	strict immutable language 'sql';

	create index foodex on foo (first(textarray));

In 7.4, the only tricky part is an extra set of parentheses. The extra parentheses are needed to isolate the expression from a possible operator class specification in the main parentheses. In 7.4, you can index an array element directly like this:

	create index foodex on foo ((textarray[3]));

Contributors: Steve Crawford scrawford at pinpointresearch.com, Tom Lane tgl at ss.pgh.pa.us, Randolf Richardson rr at 8x.ca, Greg Stark gsstark at mit.edu

Jonathan Bartlett's Explanation of WAL [GENERAL] What is WAL used for? 26-Nov-2003

In response to the question, "What is WAL [write ahead logging] used for?" Jonathan Bartlett wrote a very nice, understandable description of WAL. I'm including it here with very little editing.

WAL is write-ahead logging. Basically, before the database actually performs an operation, it writes in a log what it's about to do. Then, it goes and does it. This ensures data consistency. Let's say that the computer was powered off suddenly. There are several points that could happen:

1) Before a write - in this case the database would be fine with or without write-ahead logging.

2) During a write - without write-ahead logging, if the machine is powered off during a write, the database has no way of knowing what remained to be written, or what was being written. With PostgreSQL, this is further broken down into two possibilities:

• The power-off occurred while it was writing to the log - in this case, the log is rolled back. The database is unaffected because the data was never written to the database proper.
• The power-off occurred after writing to the log, while writing to disk - in this case, PostgreSQL can simply read from the log what was supposed to be written, and complete the write.

3) After a write - again, this does not affect PostgreSQL either with or without WAL.

In addition, WAL increases PostgreSQL's efficiency, because it can delay random-access writes to disk, and just do sequential writes to the log for a long time. This reduces the amount of head-seek the disk are doing. If you store your WAL files on a different disk, you get even more speed advantages.

Contributors: Relaxin noname at spam.com, Jonathan Bartlett johnnyb at eskimo.com, Thierry Missimilly THIERRY.MISSIMILLY at BULL.NET