Обсуждение: expressions operating on arrays
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/
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
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
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/
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
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
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/
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
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
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
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
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