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 )....
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
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.
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';
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;
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.
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