Re: pl/pgsql question (functions)

Greetings,

On Thu, 21 Jun 2001, Richard Huxton wrote:
>> I have two tables:
>> - 'hosts': containing 'hostname' (primary key) and 'ip_address' (unique
>> and non null).
>> - 'aliases': containing 'aliasname' (primary key) and 'hostname'(unique
>> and non null).
>> - aliasname.hostname is a foreign reference to hosts.hostname

>> Further, I've set up a trigger function in both hosts and aliases; so that
>> a name cannot be in both tables.
> 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?

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

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

> > For some reason, I get the feeling I have the wrong 'concept' of functions
> > in pl/pgsql.
> > I am using functions in the wrong 'way'?
> No - I think you're on the right track. The difference (if I understand
> this) is that NOT FOUND is checking for an empty set of records, whereas
> plpgsql can't return a set of records (at the moment) - you need SQL
> functions for that.
OK. I wanted to make a function that 'mimicks' (?) the behave of a
(normal) SELECT as much as possible.

Apparently that is not (yet) possible.

Cheerio! Kr. Bonne.
--
KB905-RIPE Belgacom IP networking
(c=be,a=rtt,p=belgacomgroup,s=Bonne,g=Kristoff) Internet, IP and IP/VPN
kristoff(dot)bonne(at)skypro(dot)be Faxbox : +32 2 2435122