Thread: pl/pgsql question (functions)
Greetings, I am new to pl/pgsqm (I did some SQL programming at school, now 8 years ago so that part is rusty too ;-)) Anycase, I have a question concerning creating your own functions in pl/pgsql. This is my situation: 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. So far, so good. Now, I would like to create a function 'get_ip_address' that ... euh ... retrieves an IP-address (one argument: hostname). - If the hostname is in the table 'hosts', return the corresponding IP-address. - If the hostname is in the table 'aliases', return the IP-address of the corresponding hostname in 'hosts'. - If the hostname is in neither table, return nothing. (See program below): The 'problem' is in the case where the data in in neither field. How do I program this? - If there is no 'RETURN' statement for that case; I get an error 'function terminated without RETURN'. - Just <RETURN>, <RETURN ""> or <RETURN ''> all produce an syntax-error. - When I do '<RETURN ret>' (ret being the result of the last query, being the query in the 'aliases' table; I do NOT get an error; but the function does return something (an empty row). This I don't like for two reasons: 1/ When you do 'select ... from ... where ...', and the query doesn't 'find' anything; you get NOTHING (no rows). When I do get_ip_addr('something_that_does_not_exist'); I do get SOMETHING: one row (containing an empty field). 2/ When another function uses the "get_ip_addr('some_host')" function; I cannot use 'IF NOT FOUND ...'; as -even when 'some_host' does not exist, the function returns something. (hence, the 'IF NOT FOUND' case is never followed). So, does anybody any idea how to 'fix' this? 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'? Here's the program: --- cut here --- begin --- cut here --- DECLARE ret inet; BEGIN select into ret ipaddr from hosts where hostname = $1; IF FOUND THEN RETURN ret; ELSE select into ret ipaddr from hosts,aliases where hosts.hostname = aliases.hostname AND aliases.aliasname = $1; IF FOUND THEN RETURN ret; ELSE -- Problem, what should I put here ??? RETURN; END IF; END IF; END; --- cut here --- end --- cut here --- Cheerio! Kr. Bonne. -- KB905-RIPE Belgacom IP networking (c=be,a=rtt,p=belgacomgroup,s=Bonne,g=Kristoff) Internet, IP and IP/VPN kristoff.bonne@skypro.be Faxbox : +32 2 2435122
From: "Kristoff Bonne" <kristoff.bonne@skypro.be> > Greetings, > > I am new to pl/pgsqm (I did some SQL programming at school, now 8 years > ago so that part is rusty too ;-)) Kristoff - I know exactly how you feel. If only I'd paid more attention when they were explaining what 17th normal form was... > Anycase, I have a question concerning creating your own functions in > pl/pgsql. > > This is my situation: > > 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. > So far, so good. > > Now, I would like to create a function 'get_ip_address' that ... euh ... > retrieves an IP-address (one argument: hostname). > - If the hostname is in the table 'hosts', return the corresponding > IP-address. > - If the hostname is in the table 'aliases', return the IP-address of the > corresponding hostname in 'hosts'. > - If the hostname is in neither table, return nothing. Are you sure you want a function here? Have you considered a view which does a UNION of the results from both tables? SELECT ip_addr FROM host_to_ip_view WHERE host='any.box.bigcorp.com'; will be rewritten on the fly and should be at least as fast as using a function. Not sure about ordering of IPs when there are multiple entries though. > (See program below): > > The 'problem' is in the case where the data in in neither field. How do I > program this? > > - If there is no 'RETURN' statement for that case; I get an error > 'function terminated without RETURN'. Yep - if you say you return an IP address you need to return one. > - Just <RETURN>, <RETURN ""> or <RETURN ''> all produce an syntax-error. Not valid as IP addr. > - When I do '<RETURN ret>' (ret being the result of the last query, being > the query in the 'aliases' table; I do NOT get an error; but the function > does return something (an empty row). > > This I don't like for two reasons: > 1/ When you do 'select ... from ... where ...', and the query doesn't > 'find' anything; you get NOTHING (no rows). > > When I do get_ip_addr('something_that_does_not_exist'); I do get > SOMETHING: one row (containing an empty field). > > 2/ When another function uses the "get_ip_addr('some_host')" function; I > cannot use 'IF NOT FOUND ...'; as -even when 'some_host' does not exist, > the function returns something. (hence, the 'IF NOT FOUND' case is never > followed). > > So, does anybody any idea how to 'fix' this? 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... > 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. Your options would seem to be: 1. Return NULL and test for that 2. Build a view 3. Rewrite the function as SQL Any of the above should be fine. HTH - Richard Huxton
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.bonne@skypro.be Faxbox : +32 2 2435122
From: "Kristoff Bonne" <kristoff.bonne@skypro.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