pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kristoff Bonne <kristoff.bonne@skypro.be>
To: pgsql-sql@postgresql.org
Subject: pl/pgsql question (functions)
Date: Wed, 20 Jun 2001 20:44:05 +0200 (CEST)
Message-ID: <Pine.LNX.4.30.0106202026240.8246-100000@frigg.belbone.net> (raw)

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




view thread (4+ messages)  latest in thread

Message-ID: <Pine.LNX.4.30.0106202026240.8246-100000@frigg.belbone.net>
Permalink:  ../Pine.LNX.4.30.0106202026240.8246-100000@frigg.belbone.net/
Also on:    postgresql.org/message-id/Pine.LNX.4.30.0106202026240.8246-100000@frigg.belbone.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: kristoff.bonne@skypro.be
  Subject: Re: pl/pgsql question (functions)
  In-Reply-To: <Pine.LNX.4.30.0106202026240.8246-100000@frigg.belbone.net>
  References: <Pine.LNX.4.30.0106202026240.8246-100000@frigg.belbone.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox