Received: from finch-post-10.mail.demon.net (finch-post-10.mail.demon.net [194.217.242.38]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5MBXFa19167 for ; Fri, 22 Jun 2001 07:33:15 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by finch-post-10.mail.demon.net with esmtp (Exim 2.12 #1) id 15DPBQ-0002G5-0A; Fri, 22 Jun 2001 11:33:05 +0000 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 3913B13D22; Fri, 22 Jun 2001 12:26:27 +0100 (BST) Message-ID: <002501c0fb0e$2ca504e0$1001a8c0@archonet.com> From: "Richard Huxton" To: "Kristoff Bonne" Cc: References: Subject: Re: pl/pgsql question (functions) Date: Fri, 22 Jun 2001 11:51:05 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200106/354 X-Sequence-Number: 3641 From: "Kristoff Bonne" > > You could use a constraint (foreign key) and get PG to do the triggers for > > you. > > Euh, ... > I have a foreign key from aliases.hostname to hosts.hostname; but I wanted > to make sure a host cannot be in hosts.hostname and in aliases.aliasname > (the primary keys of both tables). > > I do not think there exists a possibility to do this not using a > trigger-function. Correct? Ah - you will need a custom trigger then. Have you considered keeping all the hostnames in one table? Use a flag to distinguish the canonical ones. Whenever I need to use a UNION I end up wondering if I should put the data in the same table. > > Are you sure you want a function here? Have you considered a view which does > > a UNION of the results from both tables? > When I try to make the view, I get 'UNIONS in views not yet implemented' > (or something like that). > > (I use the Postgresql that comes part of the 'ports'-tree of FreeBSD 4.2 > RELEASE; version 7.0.2). Unions in views is 7.1 IIRC. See if you can't upgrade to 7.0.3 at least - check the history/changes for details of the bugs in 7.0.2 - I seem to remember there was a nasty one lurking in there. > > > I think you probably want "return NULL" for this case. That's what NULL was > > invented for and it is easy to test for: > > myip := get_ip_addr('some_host'); > > if myip is null then... > > This worked best. Thanks! Excellent - thought it might. - Richard Huxton