Re: pl/pgsql question (functions)

From: "Kristoff Bonne" <kristoff(dot)bonne(at)skypro(dot)be>

> > 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