Обсуждение: Array: comparing first N elements?

Hello

create or replace function eqn(anyarray, anyarray, int)
returns boolean as $$ select not exists(select $1[i] from generate_series(1,$3) g(i)                          except
                     select $2[i] from generate_series(1,$3) g(i)) 
$$ language sql immutable strict;

postgres=# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 3);eqn
-----t
(1 row)

Time: 1,590 ms
postgres=# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 4);eqn
-----f
(1 row)

regards
Pavel Stehule

2009/5/12 David Garamond <davidgaramond@gmail.com>:
> I have a "materialized path" tree table like this (simplified):
> CREATE TABLE product (
>     id SERIAL PRIMARY KEY,
>     parents INT[] NOT NULL,
>     name TEXT NOT NULL,
>     UNIQUE (parents, name)
> );
> CREATE INDEX name ON product(name);
>
> Previously I use TEXT column for parents, but arrays look interesting and
> convenient so I'm considering migrating to arrays. However, how do I rewrite
> this using arrays?
> SELECT * FROM product
> WHERE parents LIKE '0001/0010/%';
> In other words, testing against the first N elements in an array.
> Regards,
> Dave

On Tue, May 12, 2009 at 4:05 AM, David Garamond <davidgaramond@gmail.com> wrote:
> Previously I use TEXT column for parents, but arrays look interesting and
> convenient so I'm considering migrating to arrays. However, how do I rewrite
> this using arrays?
> SELECT * FROM product
> WHERE parents LIKE '0001/0010/%';
> In other words, testing against the first N elements in an array.

SELECT * FROM product
WHERE parents[1] = 1 AND parents[2] = 2;

I'd expect there to be a way to index this, on individual components
or a slice, eg.

CREATE INDEX parents_1 ON product(parents[1]);
CREATE INDEX parents_2to4 ON product(parents[2], parents[3], parents[4]);

... but this throws a parse error.  I don't have an immediate need for
this, but I'm curious if this is possible--it seems a natural part of
having a native array type.

-- 
Glenn Maynard

Στις Tuesday 12 May 2009 11:05:28 ο/η David Garamond έγραψε:
> I have a "materialized path" tree table like this (simplified):
>
> CREATE TABLE product (
>     id SERIAL PRIMARY KEY,
>     parents INT[] NOT NULL,
>     name TEXT NOT NULL,
>     UNIQUE (parents, name)
> );
> CREATE INDEX name ON product(name);
>
> Previously I use TEXT column for parents, but arrays look interesting and
> convenient so I'm considering migrating to arrays. However, how do I rewrite
> this using arrays?
>

Hi, I have used *exactly* the same scheme to model all PMS data in out fleet comprising
of 1.5 million rows, for some 6 years now.
You may find it in literature as genealogical tree representation.

> SELECT * FROM product
> WHERE parents LIKE '0001/0010/%';

Node 0001/0010 will have an id lets call it "parid".
If you model your path in parents[] (we also use the same column name!)
starting from the immediate father at parents[1] and going up to the root at parents[#parents]
then what you actually want is to find all nodes for which parents[1]=parid

you would want to look at the intarray contrib package for index suppor and many other goodies,
also you might want to write fucntions first(parents), last(parents) and then have an index
on those as well.
This way searching for the direct children of a node is very fast.

If on the other hand you want to find all children of parid, regardless of level,
then you would do that with: intset(parid) ~ parents
For the above to be efficient you should create an index on parents. Prefer method "gin" with opclass "gin__int_ops"

Well thats how i implemented trees in postgresql anyway.

>
> In other words, testing against the first N elements in an array.
>
> Regards,
> Dave
>



--
Achilleas Mantzios