Postgres Pro
Downloads
Home   >   mailing lists

Thread: searching cidr/inet arrays

searching cidr/inet arrays

From
Rob Casson
Date:
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.....

Re: searching cidr/inet arrays

From
Michael Fuhr
Date:
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/

UPDATE WITH ORDER BY

From
Rodrigo Carvalhaes
Date:
<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.

Re: UPDATE WITH ORDER BY

From
Christoph Haller
Date:
> 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

Re: UPDATE WITH ORDER BY

From
Rodrigo Carvalhaes
Date:
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.

Re: UPDATE WITH ORDER BY

From
"Greg Sabino Mullane"
Date:
-----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-----