Обсуждение: Newbie, Howto access Array-Slots in user defined functions?

Hi,

I am new to this list, maybe my question is already answered, but
I did not find any hint.

I have PostgreSQL 6.3 running, a table contains, among others, a
column with an array of bytes (c bytea[4]). Trying to do a

    SELECT DISTINCT c FROM table;

I get

ERROR:  There is no operator '<' for types '_bytea' and '_bytea'
        You will either have to retype this query using an explicit cast,
        or you will have to define the operator using CREATE OPERATOR

To create an operator I need a function, so let's beginn with a
compare function:

    CREATE FUNCTION lessbyte (_bytea, _bytea) RETURNS bool AS
    'SELECT $1[1] < $[2];' LANGUAGE 'sql';

I get

    ERROR:  parser: parse error at or near "["

Any hint, how to overcome this?

Martin

--
Martin Jacobs * Windsbach * 100.179370@germanynet.de und
martin.jacobs@an-netz.de
Registered Linux User #87175

>
> SELECT DISTINCT c FROM table;
>
>I get
>
>ERROR:  There is no operator '<' for types '_bytea' and '_bytea'
>        You will either have to retype this query using an explicit cast,
>        or you will have to define the operator using CREATE OPERATOR
>
>To create an operator I need a function, so let's beginn with a
>compare function:
>
> CREATE FUNCTION lessbyte (_bytea, _bytea) RETURNS bool AS
> 'SELECT $1[1] < $[2];' LANGUAGE 'sql';


Shouldn't the above be:
SELECT $1[1] < $1[2] ?

You probably need the field reference in both places.  I have never used
arrays so I don't know if I'm way off base but it seems logical.

Len Morgan


>
>I get
>
> ERROR:  parser: parse error at or near "["
>
>Any hint, how to overcome this?
>
>Martin
>
>--
>Martin Jacobs * Windsbach * 100.179370@germanynet.de und
>martin.jacobs@an-netz.de
>Registered Linux User #87175
>

Hi Len,

Len Morgan schrieb:
> >
> > SELECT DISTINCT c FROM table;
> >
> >I get
> >
> >ERROR:  There is no operator '<' for types '_bytea' and '_bytea'
> >        You will either have to retype this query using an explicit cast,
> >        or you will have to define the operator using CREATE OPERATOR
> >
> >To create an operator I need a function, so let's beginn with a
> >compare function:
> >
> > CREATE FUNCTION lessbyte (_bytea, _bytea) RETURNS bool AS
> > 'SELECT $1[1] < $[2];' LANGUAGE 'sql';

Sorry, this is a typo, my code example looks like this:

    CREATE FUNCTION lessbyte (_bytea, _bytea) RETURNS bool AS
    'SELECT $1[1] < $2[1];' LANGUAGE 'sql';

Ok, this is not the complete code. To do a compare of all array
elements I have to add more comparison operations. This example
is the smallest code fragment to point out my problem.

The question at this point is, how is the syntax to access an
array element of a $n-Variable which refers to an array element?
PostgreSQL does prefex the type with an underscore to make it an
array reference, but how to access a dedicated elment, also
called slot?

Any ideas?

>
>
> Shouldn't the above be:
> SELECT $1[1] < $1[2] ?
>
> You probably need the field reference in both places.  I have never used
> arrays so I don't know if I'm way off base but it seems logical.
> ...
>
> >
> >I get
> >
> > ERROR:  parser: parse error at or near "["
> >
> >Any hint, how to overcome this?
> ...

--
Martin Jacobs * Windsbach * 100.179370@germanynet.de und
martin.jacobs@an-netz.de
Registered Linux User #87175

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';

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

            regards, tom lane

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

100.179370@germanynet.de (Martin Jacobs) writes:
> Thank your for this hint, but my 6.3.2 installation does not know
> a function get_byte().

6.3.2?  You didn't mention that before.  There are a ton of
array-related bugs in 6.3.2.  Try a more recent release.

            regards, tom lane