Postgres Pro
Downloads
Home   >   mailing lists

Thread: expressions operating on arrays

expressions operating on arrays

From
h012@ied.com
Date:
Hi,
 I would like to say:

create table test1 (array1 int4[]);
insert into test1 values ('{123,234,345}');
insert into test1 values ('{456,567,678}');
now what I don't know how to do:

-- consider contents of array:
select * from test1 where array1 CONTAINS 567;

-- APPEND '789' to array in second row:
update test1 set array1=(select array1 from test1 where array1 CONTAINS 
'567' UNION select '789');

How do I go about expressing and operating on the contents of an array ?

   Thanks,
      John

-- 
-- Gospel of Jesus is the saving power of God for all who believe --              ## To some, nothing is impossible. ##
                  http://Honza.Vicherek.com/

Re: expressions operating on arrays

From
Achilleus Mantzios
Date:
look at /usr/local/src/postgresql-7.2.1/contrib/intarray

-- 
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

Re: expressions operating on arrays

From
Oleg Bartunov
Date:
Look at contrib/intarray. You'll get an index access as a bonus
Oleg
On Fri, 9 Aug 2002 h012@ied.com wrote:

>
>
>  Hi,
>
>   I would like to say:
>
> create table test1 (array1 int4[]);
> insert into test1 values ('{123,234,345}');
> insert into test1 values ('{456,567,678}');
>
>  now what I don't know how to do:
>
> -- consider contents of array:
> select * from test1 where array1 CONTAINS 567;
>
> -- APPEND '789' to array in second row:
> update test1 set array1=(select array1 from test1 where array1 CONTAINS
> '567' UNION select '789');
>
>
>  How do I go about expressing and operating on the contents of an array ?
>
>
>     Thanks,
>
>        John
>
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Re: expressions operating on arrays

From
h012@ied.com
Date:
thanks for the pointer, I'm now able to use the operators in expressions!
w.r.t. modifying the array contents: I looked through 
/usr/lib/pgsql/contrib/intarray/_int.sql.in , but it didn't make me any 
smarter. Are the "..._union" and "..._picksplit" functions supposed to add 
into and remove elements from the arrays ? How would one update a row, 
then, to add an element into one array and remove an element from another?

create table t(id int4[], txt text[]);
update t set id=g_int_union(t.id, '{555}') where t.id *= 444; -- ??
update t set text=g_int_picksplit(t.text, '{"removeme"}') where t.text *= "removeme"; -- ??
thx,
 John

On Fri, 9 Aug 2002, Oleg Bartunov wrote:

> Look at contrib/intarray. You'll get an index access as a bonus
> 
>     Oleg

On Fri, 9 Aug 2002, Achilleus Mantzios wrote:

> 
> look at /usr/local/src/postgresql-7.2.1/contrib/intarray
> 
> 

> On Fri, 9 Aug 2002 h012@ied.com wrote:
> 
> >
> >
> >  Hi,
> >
> >   I would like to say:
> >
> > create table test1 (array1 int4[]);
> > insert into test1 values ('{123,234,345}');
> > insert into test1 values ('{456,567,678}');
> >
> >  now what I don't know how to do:
> >
> > -- consider contents of array:
> > select * from test1 where array1 CONTAINS 567;
> >
> > -- APPEND '789' to array in second row:
> > update test1 set array1=(select array1 from test1 where array1 CONTAINS
> > '567' UNION select '789');
> >
> >
> >  How do I go about expressing and operating on the contents of an array ?
> >
> >
> >     Thanks,
> >
> >        John

-- 
-- Gospel of Jesus is the saving power of God for all who believe --              ## To some, nothing is impossible. ##
                  http://Honza.Vicherek.com/

Re: expressions operating on arrays

From
Tom Lane
Date:
h012@ied.com writes:
>  w.r.t. modifying the array contents: I looked through 
> /usr/lib/pgsql/contrib/intarray/_int.sql.in , but it didn't make me any 
> smarter. Are the "..._union" and "..._picksplit" functions supposed to add 
> into and remove elements from the arrays ?

No, those are support functions for GIST indexes on intarrays.  They're
not useful to call directly.
        regards, tom lane

Re: expressions operating on arrays

From
Oleg Bartunov
Date:
Hmm,

you dont' need to use GiST supporting functions !
We've posted a patch to current CVS, it has everything you need.
Please, check http://www.sai.msu.su/~megera/postgres/gist/code/7.3/README.intarray

It looks like that patch should works with 7.2 also.
Oleg
On Fri, 9 Aug 2002 h012@ied.com wrote:

>
>  thanks for the pointer, I'm now able to use the operators in expressions!
>
>  w.r.t. modifying the array contents: I looked through
> /usr/lib/pgsql/contrib/intarray/_int.sql.in , but it didn't make me any
> smarter. Are the "..._union" and "..._picksplit" functions supposed to add
> into and remove elements from the arrays ? How would one update a row,
> then, to add an element into one array and remove an element from another?
>
> create table t(id int4[], txt text[]);
> update t set id=g_int_union(t.id, '{555}') where t.id *= 444; -- ??
> update t set text=g_int_picksplit(t.text, '{"removeme"}') where t.text *= "removeme"; -- ??
>
>  thx,
>
>   John
>
> On Fri, 9 Aug 2002, Oleg Bartunov wrote:
>
> > Look at contrib/intarray. You'll get an index access as a bonus
> >
> >     Oleg
>
> On Fri, 9 Aug 2002, Achilleus Mantzios wrote:
>
> >
> > look at /usr/local/src/postgresql-7.2.1/contrib/intarray
> >
> >
>
> > On Fri, 9 Aug 2002 h012@ied.com wrote:
> >
> > >
> > >
> > >  Hi,
> > >
> > >   I would like to say:
> > >
> > > create table test1 (array1 int4[]);
> > > insert into test1 values ('{123,234,345}');
> > > insert into test1 values ('{456,567,678}');
> > >
> > >  now what I don't know how to do:
> > >
> > > -- consider contents of array:
> > > select * from test1 where array1 CONTAINS 567;
> > >
> > > -- APPEND '789' to array in second row:
> > > update test1 set array1=(select array1 from test1 where array1 CONTAINS
> > > '567' UNION select '789');
> > >
> > >
> > >  How do I go about expressing and operating on the contents of an array ?
> > >
> > >
> > >     Thanks,
> > >
> > >        John
>
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Re: expressions operating on arrays

From
h012@ied.com
Date:
Great ! I'll try patching it.
Thanks Oleg & Teodor for doing all this great work !
Long live PostgreSQL !
   see yea,
     John


On Fri, 9 Aug 2002, Oleg Bartunov wrote:

> Hmm,
> 
> you dont' need to use GiST supporting functions !
> We've posted a patch to current CVS, it has everything you need.
> Please, check http://www.sai.msu.su/~megera/postgres/gist/code/7.3/README.intarray
> 
> It looks like that patch should works with 7.2 also.
> 
>     Oleg
> On Fri, 9 Aug 2002 h012@ied.com wrote:
> 
> >
> >  thanks for the pointer, I'm now able to use the operators in expressions!
> >
> >  w.r.t. modifying the array contents: I looked through
> > /usr/lib/pgsql/contrib/intarray/_int.sql.in , but it didn't make me any
> > smarter. Are the "..._union" and "..._picksplit" functions supposed to add
> > into and remove elements from the arrays ? How would one update a row,
> > then, to add an element into one array and remove an element from another?
> >
> > create table t(id int4[], txt text[]);
> > update t set id=g_int_union(t.id, '{555}') where t.id *= 444; -- ??
> > update t set text=g_int_picksplit(t.text, '{"removeme"}') where t.text *= "removeme"; -- ??
> >
> >  thx,
> >
> >   John
> >
> > On Fri, 9 Aug 2002, Oleg Bartunov wrote:
> >
> > > Look at contrib/intarray. You'll get an index access as a bonus
> > >
> > >     Oleg
> >
> > On Fri, 9 Aug 2002, Achilleus Mantzios wrote:
> >
> > >
> > > look at /usr/local/src/postgresql-7.2.1/contrib/intarray
> > >
> > >
> >
> > > On Fri, 9 Aug 2002 h012@ied.com wrote:
> > >
> > > >
> > > >
> > > >  Hi,
> > > >
> > > >   I would like to say:
> > > >
> > > > create table test1 (array1 int4[]);
> > > > insert into test1 values ('{123,234,345}');
> > > > insert into test1 values ('{456,567,678}');
> > > >
> > > >  now what I don't know how to do:
> > > >
> > > > -- consider contents of array:
> > > > select * from test1 where array1 CONTAINS 567;
> > > >
> > > > -- APPEND '789' to array in second row:
> > > > update test1 set array1=(select array1 from test1 where array1 CONTAINS
> > > > '567' UNION select '789');
> > > >
> > > >
> > > >  How do I go about expressing and operating on the contents of an array ?
> > > >
> > > >
> > > >     Thanks,
> > > >
> > > >        John
> >
> >
> 
>     Regards,
>         Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
> 

-- 
-- Gospel of Jesus is the saving power of God for all who believe --              ## To some, nothing is impossible. ##
                  http://Honza.Vicherek.com/

How to update record in a specified order

From
Jean-Luc Lachance
Date:
Hi all,

I want to update a field with a 'NEXTVAL', but I want the record updated
in a specific order.
Any simple way of doing this other than having to create a temp table?

JLL

Re: How to update record in a specified order

From
Josh Berkus
Date:
JLL,

> I want to update a field with a 'NEXTVAL', but I want the record updated
> in a specific order.
> Any simple way of doing this other than having to create a temp table?

Please be more speciifc.  What do you mean, "specified order"?


--
-Josh BerkusAglio Database SolutionsSan Francisco

Re: How to update record in a specified order

From
Jean-Luc Lachance
Date:
As in an order by clause... If it existed.

Josh Berkus wrote:
> 
> JLL,
> 
> > I want to update a field with a 'NEXTVAL', but I want the record updated
> > in a specific order.
> > Any simple way of doing this other than having to create a temp table?
> 
> Please be more speciifc.  What do you mean, "specified order"?
> 
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: How to update record in a specified order

From
"Dan Langille"
Date:
On 9 Aug 2002 at 14:37, Josh Berkus wrote:

> JLL,
> 
> > I want to update a field with a 'NEXTVAL', but I want the record updated
> > in a specific order.
> > Any simple way of doing this other than having to create a temp table?
> 
> Please be more speciifc.  What do you mean, "specified order"?

My reading of what is required:

- sort a given result set according to some criteria
- then do something like UPDATE table SET myfield = NEXTVAL
- each row in the result set would get a value one more than the previous row

-- 
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php

Re: How to update record in a specified order

From
Josh Berkus
Date:
JLL,

So, you want to update a field with a NEXTVAL counter, but with the counter
ordered by another column?

If so, you will have to use a procedure.   Ordering your UPDATEs is not part
of SQL -- it requires a procedural element.   Here's a simple procedure (you
debug it):

CREATE PROCEDURE add_my_table_counter ()
RETURNS TEXT AS '
DECLARE v_rec RECORD;
BEGINWHILE v_rec IN SELECT * FROM my_table ORDER BY last_name LOOP    UPDATE my_table SET counter_field =
NEXTVAL(''my_sequence'')   WHERE my_table.id = v_rec.id;END LOOP;RETURN ''Done updating.''; 
END;'
LANGUAGE 'plpgsql';

--
-Josh BerkusAglio Database SolutionsSan Francisco