This is PostgreSQL General Bits issue number 20! There have been 120 items covering about that many threads in pgsql-general. Those threads contained about 642 messages. But those numbers do not count all of the messages read, tests run and research done.

I'd like to thank the active contributors to pgsql-general as well as the question askers for providing interesting and useful topics and information to write about. I'd also like to thank the people who have given me excellent feedback and suggestions.

With this issue, I would like to ask two things of you. First, feedback is invaluable to me. There are some questions in issue #10 if you are at a loss for what to say, but all feedback and comments help ensure the content of General Bits is useful. Secondly, I recently added a way for you to contribute to this small open source project via voluntary subscriptions. This is a good time to act on that and help insure that I have the time to continue this column for another twenty issues.

Editor: elein@varlena.com

Validating Date Input [GENERAL] Dates in inserts 01-Apr-2003

PostgreSQL is so generous about formatting incoming dates that it creates a bit of a hazard. Mistyped dates are rearranged until they make "sense" when ever possible. In some cases, however, it is beneficial for people alternating between mm-dd-yy and dd-mm-yy format.

The DATESTYLE option is used primarily for output. It is used on input only when the interpretation could be ambiguous--but then it is not always used. The DATESTYLE can be seen by using SHOW DATESTYLE; in psql. The DATESTYLE used in the examples below is ISO with US (NonEuropean) conventions

Here PostgreSQL assumes that 13 cannot possibly be a month, therefore, it must be the day whether mm.dd.yy or dd.mm.yy is used as the input format.

	select '13.01.03'::timestamp; 
	--> 2003-01-13 00:00:00                

	select '01.13.03'::timestamp; 
	--> 2003-01-13 00:00:00

Trying another approach, using to_timestamp doesn't help. But it is more fanciful in its interpretation. In this case, because month is 13, then it is assume to mean (I am guessing) January of the following year. Yikes!

	select to_timestamp( '07.13.03', 'DD.MM.YY' ); 
	--> 2004-01-07 00:00:00-08                     

Using the character forms of month seems to work a little better. At least it errors out when an invalid date is entered and it is forgiving on capitalization.

	select to_timestamp( '01 13 03', 'DD Mon YY' );
	--> ERROR:  to_timestamp(): bad value for MON/Mon/mon

	select to_timestamp( '01-dE-2003', 'DD-Mon-YYYY' );
	--> ERROR:  to_timestamp(): bad value for MON/Mon/mon

	select to_timestamp( '31-dEc-2003', 'DD-Mon-YYYY' ); 
	--> 2003-12-31 00:00:00-08

Consistency is the key. If dates are to be input by people, it is important to hint to them what format is expected. This does not prevent typographical errors, but it helps. The international format DD-Mon-YYYY, with or without your favorite punctuation is a very good format to use because it is easy to read and unambiguous.

However, to be absolutely sure about crucial dates, validate them on the client side. This is lousy, but true.

Contributors: Michal Kalanski kalanskim at zetokatowice.pl Martijn van Oosterhout kleptog at svana.org Nigel J. Andrews nandrews at investsystems.co.uk Tom Lane tgl at sss.pgh.pa.us scott.marlowe scott.marlowe at ihs.com elein at varlena.com

IP filters for PostgreSQL lists [GENERAL] anyone know what the deal with 64.117.224.149 is? 02-Apr-2003

A new host was added to pgsql.com with the ip of 64.117.224.149. If you use an IP based whitelist filter to get the pgsql mailing lists, you will want to add this IP to the whitelist.

An IP based spam filter is a program monitoring incoming email. If there is a whitelist associated with it, those IPs on the whitelist are allowed. Often a spam filter has both a whitelist (accept) and a blacklist (block).

Contributors: Phil Howard phil-pgsql-general at ipal.net Tom Lane tgl at sss.pgh.pa.us Marc G. Fournier scrappy at hub.org

Returning What? From Triggers [GENERAL] before and after triggers 04-Apr-2003

Both plpgsql and plpython functions can be trigger functions. The return values are a different for the plpython functions in keeping with the plpython language. This item describes plpgsql behavior only.

As of 7.3 trigger functions need to be created to return type trigger. The previously defined return value, opaque, has been deprecated.

The information value returned from plpgsql triggers is row data and an indication of the disposition of the row. Even if NULL is returned, as is legal in certain situations, it indicates (non-)data and a disposition.

If the function is not returning NULL, it is usually returning the row structure that was made available for the function via variables NEW and OLD. INSERTS only have a NEW row. UPDATES have both NEW and OLD rows. DELETES only have an OLD row. The value returned from the function is usually NULL or NEW, depending on what the function is supposed to do.

A BEFORE trigger is fired BEFORE the row is acted upon-- before it is inserted, updated or deleted. But only the BEFORE UPDATE or BEFORE INSERT trigger can modify the row being inserted or updated. The UPDATE or INSERT can be ignored by returning NULL instead of NEW. This does not abort the transaction.

An AFTER trigger occurs after the insertion, update or deletion occurs. It cannot change the data or void the action on the particular row.

All triggers, however, can raise an error to abort the transaction.

Trigger Type	Statement	Return Value	Meaning
BEFORE	INSERT	NULL	Forget this row insert, but don't abort transaction
		NEW	Use this, possibly updated row.
	UPDATE	NULL	Forget this row update, but don't abort transaction
		NEW	Use this, possibly updated row.
		OLD	Same as returning NULL, but otherwise useless
	DELETE	NULL	Forget this row deletion, but don't abort transaction
		OLD	Delete this row.
AFTER	INSERT	NULL	Return value ignored
	UPDATE	NULL	Return value ignored
	DELETE	NULL	Return value ignored.

Contributors: Dennis Gearon gearond at cvc.net, Stephan Szabo sszabo at megazone23.bigpanda.com, Tom Lane tgl at sss.pgh.pa.us, Jan Wieck JanWieck at Yahoo.com

plpgsql Trigger Basics [GENERAL] Trigger functions 01-Apr-2003

In plpgsql trigger functions the row triggering the function is defined in SQL variables so that the values are available to the trigger functions. NEW is the row about to be inserted or updated. OLD is the row as it was before the update. This table shows which statement types have what variables available.

Statement	Variable
INSERT	NEW
UPDATE	NEW, OLD
DELETE	OLD

	NEW.id 

BEFORE triggers on INSERT and UPDATE statements allow you to update or change column values based on your trigger's criteria. In UPDATE triggers, this criteria can be based on what the row looked like before the insert started.

If the task at hand is to change or assign column values in the row in question, you simply make the assignment to the NEW variable. No separate update is required.

This is an example of a plpgsql trigger function for BEFORE UPDATE of a salary table. If salary is the field being updated, the previous salary is saved in a separate column, oldsalary and the approved by field is set to NULL so the raise can be approved by some other mechanism.

CREATE OR REPLACE FUNCTION newsal() returns TRIGGER as
'	BEGIN
		IF NEW.salary <> OLD.salary,0 THEN
			NEW.oldsalary = OLD.salary;
			NEW.approvedby = NULL;
		END IF;
		RETURN NEW;
	END;
' language 'plpgsql';

CREATE TRIGGER newsal BEFORE UPDATE on salary 
	FOR EACH ROW EXECUTE PROCEDURE newsal();

Contributors: Marc Drouin marc.drouin at fondex.ca, Tom Lane tgl at sss.pgh.pa.us, Jan Wieck JanWieck at Yahoo.com elein at varlena.com

ALTERing Defaults [GENERAL] feature request - adding columns with default value 04-Apr-2003

In order to add a column with a default value, you must first ALTER TABLE to add the column and then again to add the default. When the default is added, it will affect any rows inserted after the ALTER TABLE. It will not affect any data in existing rows. This behavior corresponds to the SQL standard. If you want to update existing rows with the new default you must do this with a separate explicit update.

Henrik Steffen suggested that a feature be added that also updates the existing rows, setting the column to the default when it was null. The problem he was encountering was that the subsequent update was very slow (affecting many rows).

However, even if the ALTER TABLE performed the update, it would be doing it exactly the same way one would do it separately. The speed of the query would not be decreased. In fact it would probably cause a larger blockage because ALTER TABLE requires an exclusive lock. This is usually not a problem because the ALTER does not affect the data. But if the update of rows were included in the operation, then it would entail holding the exclusive lock much longer than would be necessary by an ordinary ALTER followed by an update.

The implicit update of data is a little questionable. And the fact that this behavior would not help the speed problem and would not adhere to the SQL standard showed that this is not really a feature to pursue.

Note that the syntax to accept ALTER TABLE ADD COLUMN ... SET DEFAULT was available briefly in 7.1. It did not affect the row data. It either split it into the two separate ALTER statements or it "lost" the DEFAULT definition according to Stephan and Tom. The syntax was removed in 7.2 because it did not correspond to the SQL standards. Apparently DB2 does enable this syntax contrary to the SQL standard and updates the rows in a fairly timely manner.

Contributors: Henrik Steffen steffen at city-map.de, Shridhar Daithankar shridhar_daithankar at persistent.co.in, Andrew Sullivan andrew at libertyrms.info, Diogo de Oliveira Biazus diogo at ikono.com.br, Stephan Szabo sszabo at megazone23.bigpanda.com, Tom Lane tgl at sss.pgh.pa.us, Dennis Gearon gearond at cvc.net, Wayne Armstrong wdarmst at bacchus.com.au