Обсуждение: searching cidr/inet arrays
i'm having trouble figuring out how to search in inet arrays....its
been a long time since i used postgres array support, so i may just be
bone-headed......
how can i determine if a given ip address is contained in the subnet
declaration inside an array?
{134.53.25.0/24,134.53.0.0/16} {134.53.24.0/24}
i'd like to see which rows match an ip of, say, 134.53.24.2.....
thanks in advance, and sorry if this is a faq....i've googled
site:archives.postgresql.org, but haven't found my solution.....
On Mon, Apr 25, 2005 at 02:46:37PM -0400, Rob Casson wrote:
>
> i'm having trouble figuring out how to search in inet arrays....its
> been a long time since i used postgres array support, so i may just be
> bone-headed......
>
> how can i determine if a given ip address is contained in the subnet
> declaration inside an array?
>
> {134.53.25.0/24,134.53.0.0/16}
> {134.53.24.0/24}
>
> i'd like to see which rows match an ip of, say, 134.53.24.2.....
See "Row and Array Comparisons" in the "Functions and Operators"
chapter of the documentation. The following works in 7.4 and later:
CREATE TABLE foo ( id serial PRIMARY KEY, nets cidr[] NOT NULL
);
INSERT INTO foo (nets) VALUES ('{134.53.25.0/24,134.53.0.0/16}');
INSERT INTO foo (nets) VALUES ('{134.53.24.0/24}');
SELECT * FROM foo WHERE '134.53.24.2' << ANY (nets);id | nets
----+-------------------------------- 1 | {134.53.25.0/24,134.53.0.0/16} 2 | {134.53.24.0/24}
(2 rows)
SELECT * FROM foo WHERE '134.53.100.2' << ANY (nets);id | nets
----+-------------------------------- 1 | {134.53.25.0/24,134.53.0.0/16}
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
<div class="moz-text-html" lang="x-western"><tt>Hi Guys!<br /><br /> I need to make an UPDATE on a column reordering it
witha sequence using order by a description.<br /> Confusing??? Well.. Let me give an example...<br /><br /> Today, my
tableit's organized like this:<br /><br /> Code / Description<br /> 9 Orange<br /> 15 Apple<br />
1 Pear<br /> 3 Tomato<br /><br /> I wanna to reorganize (reordering the code from 1 to ... ordering by
description)<br/></tt><tt><br /> Code / Description<br /></tt><tt>1 Apple</tt><br /><tt>2
Orange<br/> 3 Pear<br /> 4 Tomato</tt><br /><tt><br /> I created a sequence but I am having no succes
touse it because UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table SET code = nextval('sequence')
ORDERBY description <br /><br /> I searched a lot on the NET without ant "tip" for my case. <br /> It's a very simple
needbut I am not able to solve it...<br /><br /> Anyone knows how I can do it?<br /><br /> Cheers,<br /><br /> -- <br
/>Rodrigo Carvalhaes<br /><br /></tt></div><br />-- <br />Esta mensagem foi verificada pelo sistema de antivírus e <br
/>acredita-se estar livre de perigo.
> Rodrigo Carvalhaes wrote:
>
> Hi Guys!
>
> I need to make an UPDATE on a column reordering it with a sequence
> using order by a description.
> Confusing??? Well.. Let me give an example...
>
> Today, my table it's organized like this:
>
> Code / Description
> 9 Orange
> 15 Apple
> 1 Pear
> 3 Tomato
>
> I wanna to reorganize (reordering the code from 1 to ... ordering by
> description)
>
> Code / Description
> 1 Apple
> 2 Orange
> 3 Pear
> 4 Tomato
>
> I created a sequence but I am having no succes to use it because
> UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table
> SET code = nextval('sequence') ORDER BY description
>
> I searched a lot on the NET without ant "tip" for my case.
> It's a very simple need but I am not able to solve it...
>
> Anyone knows how I can do it?
>
> Cheers,
>
> --
> Rodrigo Carvalhaes
>
I doubt this can be done by a single SQL command.
My approach is a function. I did:
CREATE TABLE fruittable(
fruitcode INTEGER,
fruitname TEXT
);
INSERT INTO fruittable VALUES( 9, 'Orange' );
INSERT INTO fruittable VALUES( 15, 'Apple' );
INSERT INTO fruittable VALUES( 1, 'Pear' );
INSERT INTO fruittable VALUES( 3, 'Tomato' );
SELECT * FROM fruittable ORDER BY fruitname ;fruitcode | fruitname
-----------+----------- 15 | Apple 9 | Orange 1 | Pear 3 | Tomato
CREATE OR REPLACE FUNCTION reorder_fruitcode() RETURNS INTEGER AS '
DECLAREnewcode INTEGER ;fruitrecord RECORD ;
BEGINnewcode := 1 ;FOR fruitrecord IN SELECT * FROM fruittable ORDER BY fruitname LOOP RAISE NOTICE ''fruitname is %'',
fruitrecord.fruitname; UPDATE fruittable SET fruitcode = newcode WHERE fruitname = fruitrecord.fruitname ; newcode :=
newcode+ 1 ;END LOOP ;RETURN 1;
END;
' LANGUAGE plpgsql;
SELECT reorder_fruitcode();
SELECT * FROM fruittable ORDER BY fruitname ;fruitcode | fruitname
-----------+----------- 1 | Apple 2 | Orange 3 | Pear 4 | Tomato
(4 rows)
Voila.
Regards, Christoph
Thanksyou and Franz for your help. Simple and efficient... I was blind.... The plpgsql "for" is the perfect
solution<br/><br /> It was great. Have a nice week!!!<br /><br /> Cheers,<br /><br /> Rodrigo Carvalhaes<br /><br />
ChristophHaller wrote: <blockquote cite="mid426E04FD.318032AA@rodos.fzk.de" type="cite"><blockquote type="cite"><pre
wrap="">RodrigoCarvalhaes wrote:
Hi Guys!
I need to make an UPDATE on a column reordering it with a sequence
using order by a description.
Confusing??? Well.. Let me give an example...
Today, my table it's organized like this:
Code / Description
9 Orange
15 Apple
1 Pear
3 Tomato
I wanna to reorganize (reordering the code from 1 to ... ordering by
description)
Code / Description
1 Apple
2 Orange
3 Pear
4 Tomato
I created a sequence but I am having no succes to use it because
UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table
SET code = nextval('sequence') ORDER BY description
I searched a lot on the NET without ant "tip" for my case.
It's a very simple need but I am not able to solve it...
Anyone knows how I can do it?
Cheers,
--
Rodrigo Carvalhaes
</pre></blockquote><pre wrap="">I doubt this can be done by a single SQL command.
My approach is a function. I did:
CREATE TABLE fruittable(
fruitcode INTEGER,
fruitname TEXT
);
INSERT INTO fruittable VALUES( 9, 'Orange' );
INSERT INTO fruittable VALUES( 15, 'Apple' );
INSERT INTO fruittable VALUES( 1, 'Pear' );
INSERT INTO fruittable VALUES( 3, 'Tomato' );
SELECT * FROM fruittable ORDER BY fruitname ;fruitcode | fruitname
-----------+----------- 15 | Apple 9 | Orange 1 | Pear 3 | Tomato
CREATE OR REPLACE FUNCTION reorder_fruitcode() RETURNS INTEGER AS '
DECLAREnewcode INTEGER ;fruitrecord RECORD ;
BEGINnewcode := 1 ;FOR fruitrecord IN SELECT * FROM fruittable ORDER BY fruitname LOOP RAISE NOTICE ''fruitname is %'',
fruitrecord.fruitname; UPDATE fruittable SET fruitcode = newcode WHERE fruitname = fruitrecord.fruitname ; newcode :=
newcode+ 1 ;END LOOP ;RETURN 1;
END;
' LANGUAGE plpgsql;
SELECT reorder_fruitcode();
SELECT * FROM fruittable ORDER BY fruitname ;fruitcode | fruitname
-----------+----------- 1 | Apple 2 | Orange 3 | Pear 4 | Tomato
(4 rows)
Voila.
Regards, Christoph
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to <a
class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a> so that your
messagecan get through to the mailing list cleanly
</pre></blockquote><br /><pre class="moz-signature" cols="72">--
Abraço,
Rodrigo Carvalhaes
DBA PostgreSQL
Moderador grupo siga-br</pre><br />-- <br />Esta mensagem foi verificada pelo sistema de antivírus e <br /> acredita-se
estarlivre de perigo.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
(Please send email as text, not HTML)
> I need to make an UPDATE on a column reordering it with a sequence
> using order by a description
> ...
BEGIN;
CREATE SEQUENCE fruit_seq;
CREATE TABLE newfruit AS SELECT nextval('fruit_seq')::int AS newid, * FROM fruit ORDER BY lower(description);
ALTER TABLE newfruit DROP COLUMN id;
ALTER TABLE newfruit RENAME COLUMN newid TO id;
DROP TABLE fruit;
ALTER TABLE newfruit RENAME TO fruit;
DROP SEQUENCE fruit_seq;
COMMIT;
SELECT * FROM fruit ORDER BY id ASC;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200504270805
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCb4ArvJuQZxSWSsgRAnSGAJwMbp6qoN3H2wFedsgn8N55kV6zUQCg77Qn
VWsBmACCUFIdzRDRRalG6KI=
=y3G9
-----END PGP SIGNATURE-----