Quick Links

Re: UPDATE WITH ORDER BY

Lists:	pgsql-sql

From:	Rob Casson <rob(dot)casson(at)gmail(dot)com>
To:	pgsql-sql(at)postgresql(dot)org
Subject:	searching cidr/inet arrays
Date:	2005-04-25 18:46:37
Message-ID:	fddc0c5705042511464f94be68@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

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.....

From:	Michael Fuhr <mike(at)fuhr(dot)org>
To:	Rob Casson <rob(dot)casson(at)gmail(dot)com>
Cc:	pgsql-sql(at)postgresql(dot)org
Subject:	Re: searching cidr/inet arrays
Date:	2005-04-25 20:24:06
Message-ID:	20050425202406.GA3289@winnie.fuhr.org
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

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/

From:	Rodrigo Carvalhaes <grupos(at)carvalhaes(dot)net>
To:
Cc:	pgsql-sql(at)postgresql(dot)org
Subject:	UPDATE WITH ORDER BY
Date:	2005-04-26 03:12:32
Message-ID:	426DB1A0.2000604@carvalhaes.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<div class="moz-text-html" lang="x-western">
<tt>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) 
</tt><tt> 
Code     / Description 
</tt><tt>1          Apple</tt> 
<tt>2          Orange 
3          Pear 
4          Tomato</tt> 
<tt> 
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 
 
</tt>
</div>
</body>
 --
 Esta mensagem foi verificada pelo sistema de antivírus e
 acredita-se estar livre de perigo.
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	1.8 KB

From:	Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
To:	grupos(at)carvalhaes(dot)net
Cc:	pgsql-sql(at)postgresql(dot)org
Subject:	Re: UPDATE WITH ORDER BY
Date:	2005-04-26 09:08:13
Message-ID:	426E04FD.318032AA@rodos.fzk.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

> 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 '

DECLARE
newcode INTEGER ;
fruitrecord RECORD ;

BEGIN
newcode := 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;

Regards, Christoph

From:	Rodrigo Carvalhaes <grupos(at)carvalhaes(dot)net>
To:	Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
Cc:	pgsql-sql(at)postgresql(dot)org
Subject:	Re: UPDATE WITH ORDER BY
Date:	2005-04-26 16:07:18
Message-ID:	426E6736.8030403@carvalhaes.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-2" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanksyou and Franz for your help. Simple and efficient... I was
blind.... The plpgsql "for" is the perfect solution 
 
It was great. Have a nice week!!! 
 
Cheers, 
 
Rodrigo Carvalhaes 
 
Christoph Haller wrote:
<blockquote cite="mid426E04FD(dot)318032AA(at)rodos(dot)fzk(dot)de" type="cite">
<blockquote type="cite">
<pre wrap="">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

</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 '

DECLARE
newcode INTEGER ;
fruitrecord RECORD ;

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(at)postgresql(dot)org">majordomo(at)postgresql(dot)org</a> so that your
message can get through to the mailing list cleanly

</pre>
</blockquote>
 
<pre class="moz-signature" cols="72">--

Abraço,

Rodrigo Carvalhaes
DBA PostgreSQL
Moderador grupo siga-br</pre>
</body>
 --
 Esta mensagem foi verificada pelo sistema de antivírus e
 acredita-se estar livre de perigo.
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	3.2 KB

From:	"Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To:	pgsql-sql(at)postgresql(dot)org
Subject:	Re: UPDATE WITH ORDER BY
Date:	2005-04-27 12:05:49
Message-ID:	f4c00ccf32fdcc228c81c684be9b58d5@biglumber.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-sql

-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200504270805
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCb4ArvJuQZxSWSsgRAnSGAJwMbp6qoN3H2wFedsgn8N55kV6zUQCg77Qn
VWsBmACCUFIdzRDRRalG6KI=
=y3G9
-----END PGP SIGNATURE-----