Re: Newbie, Howto access Array-Slots in user defined functions? - Mailing list pgsql-general

Tom Lane wrote:
>
> 100.179370@germanynet.de (Martin Jacobs) writes:
> >       CREATE FUNCTION lessbyte (_bytea, _bytea) RETURNS bool
> >       AS
> >       'SELECT $1[1] < $2[1];' LANGUAGE 'sql';
> > ERROR:  Unable to identify an operator '<' for types 'bytea'
> > and 'bytea'
> >         You will have to retype this query using an explicit
> >         cast
>
> There is nothing wrong with your syntax --- you've declared a function
> that takes two arrays of bytea, selects the first element of each, and
> compares 'em.  But bytea doesn't support comparison operators ... or
> much of anything, actually.  There is a get_byte function, so you could
> conceivably build what you want starting with
>
> create function lessbyte(bytea, bytea) returns bool as
> 'select get_byte($1,0) < get_byte($2,0)' language 'sql';

Thank your for this hint, but my 6.3.2 installation does not know
a function get_byte(). Instead it knows functions byteaGetByte,
byteaSetByte, ...

Sorry, but that don't work either. New problems come up, see
below.

>
> However, I don't see any reasonable way to deal with variable-length
> inputs without a loop, and SQL functions don't have looping
> constructs.
>
> Given the lack of operators, type bytea isn't currently useful for
> much except plain storage and retrieval of raw byte sequences.
> Have you got a strong reason for using bytea, rather than some
> better-supported type like text?  Heck, even array of char
> would work better:
>
> regression=# CREATE FUNCTION lessbyte(_char, _char) returns > bool as
> regression-# 'SELECT $1[1] < $2[1];' LANGUAGE 'sql';
> CREATE

Playing with some homebrown databases I tried to collect ip
adresses in a table. One possibility would be to store them in a
character array, but then I would have to enter them in escaped
notation and would see characters instead of numbers when doing a
plain select in pgsql or pgaccess.

(I know, that postgreSQL supports a native data type supporting
internet and mac adresses.)

Try the following:

        internetaccess=> create table iptest (ip bytea[4]);
        CREATE
        internetaccess=> insert into iptest (ip) values ('{192,147,68,5}');
        INSERT 44085 1
        internetaccess=> insert into iptest (ip) values ('{191,146,67,1}');
        INSERT 44086 1
        internetaccess=> select * from iptest;
        ip
        ----------------------
        {"192","147","68","5"}
        {"191","146","67","1"}
        (2 rows)

So far it's what I expected. Now let's extract the class A part
of the address:

        internetaccess=> select ip[1] from iptest;
        ERROR:  type name lookup of ip failed

Uhh, what's this? What's going wrong now? Another approach:

        internetaccess=> select "byteaGetByte"(ip,1) from iptest;
        ERROR:  function byteaGetByte(_bytea, int4) does not
exist

Ok, \df command shows that there is a function byteaGetByte which
expects as first argument a bytea and as second an int4 value.
But how can I use this function if the parser generates
references to bytea (_bytea) instead of the object itself?

> ...

Back to your question: Ip addresses are not of variable length.
Therefor it should be possible to implement the missing compare
operators for <, <=, >, >= ... by unrolling the loop by hand. I
know this is not optimal but I thought of this being an exercise
for myself to lern about PostgreSQL extensions with functions and
operators without having to implement external moduls using C/C++
and gcc.

Martin Jacobs