Re: Newbie, Howto access Array-Slots in user defined functions?

Tom Lane wrote:
>
> 100(dot)179370(at)germanynet(dot)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