pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pl/pgsql question (functions)
4+ messages / 2 participants
[nested] [flat]

* pl/pgsql question (functions)
@ 2001-06-20 18:44 Kristoff Bonne <kristoff.bonne@skypro.be>
  2001-06-21 09:25 ` Re: pl/pgsql question (functions) Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Kristoff Bonne @ 2001-06-20 18:44 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pl/pgsql question (functions)
  2001-06-20 18:44 pl/pgsql question (functions) Kristoff Bonne <kristoff.bonne@skypro.be>
@ 2001-06-21 09:25 ` Richard Huxton <dev@archonet.com>
  2001-06-22 08:09   ` Re: pl/pgsql question (functions) Kristoff Bonne <kristoff.bonne@skypro.be>
  0 siblings, 1 reply; 4+ messages in thread

From: Richard Huxton @ 2001-06-21 09:25 UTC (permalink / raw)
  To: Kristoff Bonne <kristoff.bonne@skypro.be>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pl/pgsql question (functions)
  2001-06-20 18:44 pl/pgsql question (functions) Kristoff Bonne <kristoff.bonne@skypro.be>
  2001-06-21 09:25 ` Re: pl/pgsql question (functions) Richard Huxton <dev@archonet.com>
@ 2001-06-22 08:09   ` Kristoff Bonne <kristoff.bonne@skypro.be>
  2001-06-22 10:51     ` Re: pl/pgsql question (functions) Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Kristoff Bonne @ 2001-06-22 08:09 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pl/pgsql question (functions)
  2001-06-20 18:44 pl/pgsql question (functions) Kristoff Bonne <kristoff.bonne@skypro.be>
  2001-06-21 09:25 ` Re: pl/pgsql question (functions) Richard Huxton <dev@archonet.com>
  2001-06-22 08:09   ` Re: pl/pgsql question (functions) Kristoff Bonne <kristoff.bonne@skypro.be>
@ 2001-06-22 10:51     ` Richard Huxton <dev@archonet.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Richard Huxton @ 2001-06-22 10:51 UTC (permalink / raw)
  To: Kristoff Bonne <kristoff.bonne@skypro.be>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-06-22 10:51 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-20 18:44 pl/pgsql question (functions) Kristoff Bonne <kristoff.bonne@skypro.be>
2001-06-21 09:25 ` Richard Huxton <dev@archonet.com>
2001-06-22 08:09   ` Kristoff Bonne <kristoff.bonne@skypro.be>
2001-06-22 10:51     ` Richard Huxton <dev@archonet.com>

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