pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedexpressions operating on arrays
7+ messages / 4 participants
[nested] [flat]
* expressions operating on arrays
@ 2002-08-09 04:38 h012@ied.com
0 siblings, 2 replies; 7+ messages in thread
From: h012@ied.com @ 2002-08-09 04:38 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: expressions operating on arrays
@ 2002-08-09 06:17 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: h012@ied.com
1 sibling, 1 reply; 7+ messages in thread
From: Achilleus Mantzios @ 2002-08-09 06:17 UTC (permalink / raw)
To: h012@ied.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: expressions operating on arrays
@ 2002-08-09 07:12 h012@ied.com
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 7+ messages in thread
From: h012@ied.com @ 2002-08-09 07:12 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: expressions operating on arrays
@ 2002-08-09 07:30 Oleg Bartunov <oleg@sai.msu.su>
parent: h012@ied.com
1 sibling, 0 replies; 7+ messages in thread
From: Oleg Bartunov @ 2002-08-09 07:30 UTC (permalink / raw)
To: h012@ied.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: expressions operating on arrays
@ 2002-08-09 13:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: h012@ied.com
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2002-08-09 13:53 UTC (permalink / raw)
To: h012@ied.com; +Cc: Oleg Bartunov <oleg@sai.msu.su>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: expressions operating on arrays
@ 2002-08-09 14:57 Oleg Bartunov <oleg@sai.msu.su>
parent: h012@ied.com
1 sibling, 1 reply; 7+ messages in thread
From: Oleg Bartunov @ 2002-08-09 14:57 UTC (permalink / raw)
To: h012@ied.com; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: expressions operating on arrays
@ 2002-08-09 20:49 h012@ied.com
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 7+ messages in thread
From: h012@ied.com @ 2002-08-09 20:49 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-08-09 20:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-09 04:38 expressions operating on arrays h012@ied.com
2002-08-09 06:17 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-08-09 07:12 ` h012@ied.com
2002-08-09 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-09 14:57 ` Oleg Bartunov <oleg@sai.msu.su>
2002-08-09 20:49 ` h012@ied.com
2002-08-09 07:30 ` Oleg Bartunov <oleg@sai.msu.su>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox