Apologies - just reread my post and I've confused matters with typos during the abtraction of my code. For the purposes of the example given, please read "get_colnames_for_id()" rather than "get_cathcode()"...
Cheers, Ian On Wed, Apr 2, 2008 at 5:49 PM, Ian Sillitoe <[EMAIL PROTECTED]> wrote: > This is probably a stupid question that has a very quick answer, however > it would be great if someone could put me out of my misery... > > I'm trying to JOIN two tables (well a table and a resultset from a > PL/pgsql function) where a joining column can be NULL > > > -- for a given id, return a bunch of columns that I can use for joins > > psql> select * from get_colnames_for_id('1.10.8'); > > depth1 | depth2 | depth3 | depth4 | depth5 | > --------+--------+--------+--------+--------+ > 1 | 10 | 8 | | | > (1 row) > > -- > psql> select * from table where depth1 = 1 AND depth2 = 10 AND depth3 = 8 > AND depth4 IS NULL; > > id | depth1 | depth2 | depth3 | depth4 | depth5 | name | > ---+--------+--------+--------+--------+--------+----------------------+ > 1 | 1 | 10 | 8 | | | name for node 1.10.8 | > > (1 row) > > -- I (wrongly) expected the following to be equivalent > > psql> select c.* from get_cathcode('1.10.8') c JOIN table t USING(depth1, > depth2, depth3, depth4); > > id | depth1 | depth2 | depth3 | depth4 | depth5 | name | > ---+--------+--------+--------+--------+--------+-----------+ > (0 rows) > > -- Whereas the following works... > > psql> select * from get_colnames_for_id('1.10.8.10'); > > depth1 | depth2 | depth3 | depth4 | depth5 | > --------+--------+--------+--------+--------+ > 1 | 10 | 8 | 10 | | > (1 row) > > psql> select c.* from get_cathcode('1.10.8.10') c JOIN table t > USING(depth1, depth2, depth3, depth4); > > id | depth1 | depth2 | depth3 | depth4 | depth5 | name > | > > ---+--------+--------+--------+--------+--------+-------------------------+ > 2 | 1 | 10 | 8 | 10 | | name for node 1.10.8.10| > (0 rows) > > > So, I'm currently assuming from all this that joining on t1.col = t2.col > doesn't make any sense when t1.col and t2.col are both NULL - since: > > psql> SELECT (NULL = NULL) IS TRUE; > ?column? > ---------- > f > (1 row) > > psql> SELECT (NULL IS NULL) IS TRUE; > ?column? > ---------- > t > (1 row) > > Unless I've missed something, the docs on > http://www.postgresql.org/docs/8.1/static/functions-comparison.html seem > to suggest that the concept is an example of bad programming and the > workaround (of switching on the 'transform_null_equals' config) is a hack. > Is this all true or did my logic just get screwed up at some point? Unless > I've just missed something obvious, it seems useful to be able to join two > tables based on a condition where they share a NULL column - is there > another way of doing this? > > Many thanks, > > Ian > > >