In pl/pgsql, a query which returns no rows can be tested after the selection with IF FOUND.

	BEGIN
		select into myid id from cookies where name like 'harold';
		IF FOUND THEN
			return myid;
		ELSE
			return 'not found';
		ENDIF;
	END;

	BEGIN
		select into howmuch sum(price) from stuff where invoiced='t';
		IF NOT FOUND THEN
			return 0;
		ELSE
			return howmuch;
		ENDIF;
	END;

In the cases where you want a NULL value to be represented as something else, a zero in this case, you can use the coalesce() function. Coalesce checks the first parameter for NULL and returns it if it is NOT NULL, otherwise it returns the second parameter. Using coalesce() in the above situation can eliminate the need for the (useless) IF FOUND statements.

	...select coalesce( sum(price), 0 )....

Contributors: Jean-Christian Imbeault jc at mega-bucks.co.jp, Tom Lane tgl at sss.pgh.pa.us Christoph Dalitz christoph.dalitz at hs-niederrhein.de, elein at varlena.com

Long hex values [GENERAL] hex to decimal and back again 27-Mar-2003

In order to store very large hex values and still sort and index on them, it is recommended that bytea be used as the column data type. encode() and decode() can be used to input and output values.

The original idea was to store the value in a text column, however, this makes numeric sorting difficult. Another alternative was to cast the value to an int8 for sorting, however, the value was too large for an int8.

To use bytea for these values, the table is created with the bytea data type:

	create table radiusdata(id bytea);

	create unique index idx1 on radiusdata(id);

	insert into radiusdata values (decode('2069659D9A5BBD4F8E0E99A742716F12','hex'));
	insert into radiusdata values (decode('2068659D9A5BBD4F8E0E99A742716F12','hex'));
	insert into radiusdata values (decode('2069659D9A5BBD4F8E0E99A742716F11','hex'));

	select * from radiusdata order by id;
	                     id
	------------------------------------------
	  he\235\232[½O\216\016\231§Bqo\022
	  ie\235\232[½O\216\016\231§Bqo\021
	  ie\235\232[½O\216\016\231§Bqo\022

	select encode(id, 'hex') from radiusdata order by id;
	               encode
	----------------------------------
	 2068659d9a5bbd4f8e0e99a742716f12
	 2069659d9a5bbd4f8e0e99a742716f11
	 2069659d9a5bbd4f8e0e99a742716f12

Contributors: Peter Nixon listuser at peternixon.net, Joe Conway mail at joeconway.com

Concurrency and Transactions [GENERAL] two concurrency questions 03-Mar-2003

A transaction in postgreSQL encompasses all of the functions and triggers in the transaction. If you are not in a block transaction (BEGIN ... COMMIT) the single primary statement initiates the transaction.

In this (nonsense) SQL, the primary statement is INSERT. Let us also suppose there is a BEFORE INSERT trigger on thattable which calls yet another function checkme().

	INSERT INTO thattable 
		SELECT f(id), x(sdate), y(stuff) 
		FROM mytable 
		WHERE id IN 
			(SELECT id FROM anothertable a, badaccts b 
			 WHERE a.id = b.id);

Contributors: Jeff Patterson jpat at mpip.org, Doug McNaught doug at mcnaught.org, Neil Conway neilc at samurai.com, elein at varlena.com

Conditional Constraints [GENERAL] conditional constraints 11-Apr-2003

If you have a situation where Table B has a foreign key (B.aid) defined as Table A's primary key, then all of the values entered in Table B's foreign key column must refer to an existing row in Table A.

	Table A		Table B
	=======		=========
	aid (PK)		bid (PK)
	...			aid (FK) REFERENCES A(aid)
					...

There is one exception to this rule and that is if the foreign key value in Table B (b.aid) is NULL. This is allowed as a valid value and enables a "conditional constraint". That constraint is that if there is a value in the foreign key field of Table B, then it must reference a row in Table A, otherwise it is NULL and allow it. This sleight of hand is based on the fact that NULL is not a "value".

If you want your constraint to be conditional, but not on NULL or NOT NULL, you must have a CHECK constraint instead of defining a foreign key. For example, if you wanted to allow the the value 'X' in the field B.aid and otherwise have it refer to table A's primary key (A.aid) the CHECK would call a function like this. This logic is in the checkaid() function rather than the table definition because you are not allowed to have a bare subselect in a CHECK clause.

	
create or replace function checkaid( text ) returns boolean
as '
select case when $1 = ''X'' then TRUE
	else (select case when $1 in (select aid from a)
		then TRUE
		else FALSE
	end)
end;
' language 'sql';

Contributors: tom dyson tom at torchbox.com, Martijn van Oosterhout kleptog at svana.org, Jan Wieck JanWieck at Yahoo.com, Dennis Gearon gearond at cvc.net, elein at varlena.com

Qualifications on Unions [GENERAL] Factoring where clauses through unions 04-Apr-2003

As of PostgreSQL 7.3*, qualifications of a UNION will be pushed, if possible, into the individual selections comprising the UNION. Prior to 7.3, the UNION would be performed first, followed by the qualification of the UNION.

For example if you have a view defined over a UNION:

	create view allcolors as
		select name, code from blues
		union
		select name, code from greens
		union 
		select name, code from reds;

	select name, code from allcolors where code > decode('cccccc','hex');

	select name, code from blues where code > decode('cccccc','hex');
	union
	select name, code from greens where code > decode('cccccc','hex');
	union
	select name, code from reds where code > decode('cccccc','hex');

Contributors: Jonathan Bartlett johnnyb at eskimo.com, Ed L. pgsql at bluepolka.net, Tom Lane tgl at sss.pgh.pa.us

Logging SQL [GENERAL] Log the SQL Statements 31-Mar-2003

Most people know about the ability to log SQL statements by setting log_statement in the postgresql.conf file. This setting will log all of the SQL statements sent to the backend into PostgreSQL's log file.

For that smaller or more immediate testing and tracking, you can also set this variable in a pgsql session using:

	=> SET log_statement TO TRUE; 

Contributors: David Busby busby at edoceo.com, Bruce Momjian pgman at candle.pha.pa.us, Dennis Gearon gearond at cvc.net