varlena
varlena
PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

13-Apr-2003 Issue: 21

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org.

NULL is Found
[GENERAL] PL/PGSQL question 16-Jan-2003

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;
This is extremely useful. However, be aware that aggregates (except count()) and many other functions return a NULL when called. That NULL return is counted as a ROW and IF FOUND returns true after the call. For example, the following does not return 0 if there were nothing (NULL) invoiced:
	BEGIN
		select into howmuch sum(price) from stuff where invoiced='t';
		IF NOT FOUND THEN
			return 0;
		ELSE
			return howmuch;
		ENDIF;
	END;
If there isn't any stuff invoiced, then sum(price) will return a row containing NULL. IF FOUND only checks to see if any rows were returned and does not check the contents of the row. A row was seen so IF FOUND always returns TRUE.

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);
The index is created directly on the field. A functional index is not necessary.
	create unique index idx1 on radiusdata(id);
The value is inserted using decode to change the value to hex. (These values differ in the 4th and the last characters.)
	insert into radiusdata values (decode('2069659D9A5BBD4F8E0E99A742716F12','hex'));
	insert into radiusdata values (decode('2068659D9A5BBD4F8E0E99A742716F12','hex'));
	insert into radiusdata values (decode('2069659D9A5BBD4F8E0E99A742716F11','hex'));
Selecting out the value displays octal escape sequences, but the value is stored as binary. To select the value as hex, use encode().
	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);
The execution of this statement invokes all of the triggers, functions and sub-selects in the context of the single transaction begun with INSERT. This includes the functions f(), x(), y(), the trigger function checkme() and the subselect in the last WHERE clause. Any cast functions invoked implicitly are also included, as well as any input or output functions for the data types. If there is an error in any of the SQL or auxiliary functions the transaction as a whole will be rolled back.

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;
And then if you wanted to qualify a selection, for example to find all of the "darker" colors you would issue the following statement where #cccccc is medium grey.
	select name, code from allcolors where code > decode('cccccc','hex');
In 7.3 this statement is expanded to the following:
	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');
This change is a very nice performance enhancement for UNIONs. To verify the plan used by your actual queries, use EXPLAIN ANALYZE;

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; 
This will turn the logging of statements on for just that session. This more narrow scope is very helpful for debugging while the database is being used by others. You will not have to sort out everyone else's statements which looking for your own. And you would not have had to bounce the database server to turn on the option.

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


Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Top
Google
Search General Bits & varlena.com Search WWW