date:20120301

[SQL] No sort with except

2012-03-01 Thread reto . buchli

Dear all,

When I run the following SQL with PostgreSQL 9.1:

--
SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, 
status
  FROM person
 
WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 

ORDER BY pernr, eindt DESC;
--

it works. I get the most recent persons, even if one came back within this 
time range.

But if i do this:

---
SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, 
status
  FROM person
 
WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
EXCEPT 

SELECT DISTINCT ON (pernr) pernr,  vorna, nachn, eindt, ausdt, updat, 
status
  FROM person 
   RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid = 10
WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
ORDER BY pernr, eindt DESC;
---

In this case the ORDER BY does not work: I will get the same person data, 
either with DESC as with ASC, even when this should change.

Does anyone have an explanation for this?

Many Thanks

Re: [SQL] No sort with except

2012-03-01 Thread Frank Lanitz

Am 01.03.2012 09:13, schrieb [email protected]:
> Dear all,
> 
> When I run the following SQL with PostgreSQL 9.1:
> 
> -- 
> SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status
>   FROM person
>
> WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> 
> ORDER BY pernr, eindt DESC;
> -- 
> 
> it works. I get the most recent persons, even if one came back within
> this time range.
> 
> But if i do this:
> 
> ---
> SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, status
>   FROM person
>
> WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> EXCEPT
> 
> SELECT DISTINCT ON (pernr) pernr,  vorna, nachn, eindt, ausdt, updat,
> status
>   FROM person
>RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid = 10
> WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> ORDER BY pernr, eindt DESC;
> ---
> 
> In this case the ORDER BY does not work: I will get the same person
> data, either with DESC as with ASC, even when this should change.
> 
> Does anyone have an explanation for this?


Don't you sort just the part at EXCEPT?

Cheers,
Frank


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] No sort with except

2012-03-01 Thread reto . buchli

[email protected] schrieb am 01.03.2012 09:16:53:

> From: Frank Lanitz 
> To: [email protected], 
> Date: 01.03.2012 09:16
> Subject: Re: [SQL] No sort with except
> Sent by: [email protected]
> 
> Am 01.03.2012 09:13, schrieb [email protected]:
> > Dear all,
> > 
> > When I run the following SQL with PostgreSQL 9.1:
> > 
> > -- 
> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, 
status
> >   FROM person
> > 
> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> > 
> > ORDER BY pernr, eindt DESC;
> > -- 
> > 
> > it works. I get the most recent persons, even if one came back within
> > this time range.
> > 
> > But if i do this:
> > 
> > ---
> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, 
status
> >   FROM person
> > 
> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> > EXCEPT 
> > 
> > SELECT DISTINCT ON (pernr) pernr,  vorna, nachn, eindt, ausdt, updat,
> > status
> >   FROM person
> >RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid = 
10
> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
> > ORDER BY pernr, eindt DESC;
> > ---
> > 
> > In this case the ORDER BY does not work: I will get the same person
> > data, either with DESC as with ASC, even when this should change.
> > 
> > Does anyone have an explanation for this?
> 
> 
> Don't you sort just the part at EXCEPT?
> 
> Cheers,
> Frank
> 
> 
Hi Frank
This may be. But as I understand, this will sort the result set. I'm also 
not able to place ORDER BY before the EXCEPT.

Am I wrong?

Cheers,
Reto

Re: [SQL] No sort with except

2012-03-01 Thread Philip Couling

Hi Reto

You are right to assume that you're query is ordering the second select
and not the whole query.  To order the query as a whole it in
parentheses and put the ORDER BY at the end:

(
SELECT foo FROM X
EXCEPT
SELECT foo FROM Y
) ORDER BY foo;

Hope this helps


On 01/03/2012 08:56, [email protected] wrote:
> 
> [email protected] schrieb am 01.03.2012 09:16:53:
> 
>> From: Frank Lanitz 
>> To: [email protected],
>> Date: 01.03.2012 09:16
>> Subject: Re: [SQL] No sort with except
>> Sent by: [email protected]
>>
>> Am 01.03.2012 09:13, schrieb [email protected]:
>> > Dear all,
>> >
>> > When I run the following SQL with PostgreSQL 9.1:
>> >
>> > --
>> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat,
> status
>> >   FROM person
>> >
>> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
>> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
>> >
>> > ORDER BY pernr, eindt DESC;
>> > --
>> >
>> > it works. I get the most recent persons, even if one came back within
>> > this time range.
>> >
>> > But if i do this:
>> >
>> > ---
>> > SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat,
> status
>> >   FROM person
>> >
>> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
>> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
>> > EXCEPT
>> >
>> > SELECT DISTINCT ON (pernr) pernr,  vorna, nachn, eindt, ausdt, updat,
>> > status
>> >   FROM person
>> >RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid
> = 10
>> > WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
>> > AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD')
>> > ORDER BY pernr, eindt DESC;
>> > ---
>> >
>> > In this case the ORDER BY does not work: I will get the same person
>> > data, either with DESC as with ASC, even when this should change.
>> >
>> > Does anyone have an explanation for this?
>>
>>
>> Don't you sort just the part at EXCEPT?
>>
>> Cheers,
>> Frank
>>
>>
> Hi Frank
> This may be. But as I understand, this will sort the result set. I'm
> also not able to place ORDER BY before the EXCEPT.
> 
> Am I wrong?
> 
> Cheers,
> Reto


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] No sort with except

2012-03-01 Thread Jasen Betts

On 2012-03-01, [email protected]  wrote:
> Dies ist eine mehrteilige Nachricht im MIME-Format.
> --=_alternative 002D2CF5C12579B4_=
> Content-Type: text/plain; charset="US-ASCII"
>
> Dear all,
>
> When I run the following SQL with PostgreSQL 9.1:
>
> --
> SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, 
> status
>   FROM person
>  
> WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
>
> ORDER BY pernr, eindt DESC;
> --
>
> it works. I get the most recent persons, even if one came back within this 
> time range.
>
> But if i do this:
>
> ---
> SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, 
> status
>   FROM person
>  
> WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
> EXCEPT 
>
> SELECT DISTINCT ON (pernr) pernr,  vorna, nachn, eindt, ausdt, updat, 
> status
>   FROM person 
>RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid = 10
> WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
> AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
> ORDER BY pernr, eindt DESC;
> ---
>
> In this case the ORDER BY does not work: I will get the same person data, 
> either with DESC as with ASC, even when this should change.
>
> Does anyone have an explanation for this?


the ORDER BY is evaluated over the final result set, you can bind it to the
except part using parentheses.


 SELECT DISTINCT ON (pernr) pernr, vorna, nachn, eindt, ausdt, updat, 
 status
   FROM person
  
 WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
 AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
 EXCEPT 
 (
 SELECT DISTINCT ON (pernr) pernr,  vorna, nachn, eindt, ausdt, updat, 
 status
   FROM person 
RIGHT JOIN table ON sobid = CAST (pernr AS VARCHAR) AND objid = 10
 WHERE eindt <= TO_CHAR(CURRENT_DATE,'MMDD')
 AND ausdt >= TO_CHAR(CURRENT_DATE,'MMDD') 
 ORDER BY pernr, eindt DESC;
 )



-- 
⚂⚃ 100% natural


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Aggregate and join problem

2012-03-01 Thread Swärd Mårten

Hi folks
I have some troubles to create a SQL-query and my hope is that someone of you 
could help me with this..
It's somewhat difficult to explain what I want to do but I'll give it a try and 
see if you can understand the problem.. Ahh fuck this.. It's almost imposible 
to explain.. :) I don't understand what it mys self after I have written it.. 
Read and see if you understand.. :)

I have three tables:

Table_1:
A table with meta data for areas (names, geometries and so..). Every area has a 
unique id.
Example:
101 | 'small area' | 'area name' | geom
102 | 'small area' | 'area name' | geom.
103 | 'small area' | 'area name' | geom.
104 | 'LARGE area' | 'area name' | geom

Table 2.
A table with values for some smaller areas. Contains a reference to an id in 
table1 and a value
Example:
101 | 12.5
102 | 5.5
103 | 6.5


Table_3:
A cross reference table with id:s for witch areas are connected to each other. 
Eg. What smaller areas that's is inside a larger area.
A larger area could have many smaller areas connected to it
Contains a reference to table 1 for the smaller areas and a reference to table 
1 for the larger area.
Example:
101 | 104
102 | 104
103 | 104


What I want to do is the following:
The larger area should "inherit" the lowest value from the smaller areas that 
are connected to it.

I want to be able to get all larger areas and let them have a value that is the 
lowest value from table 2.
If you look at the example data I only want to get the larger area (104) from 
table 1 with a value from table 2 that is the lowest value of the  areas 
connected to id 104. The result would be: 104 | 'LARGE area' | 'area name' | 
geom. | (value from table 2 id 102)

Best regards, Mårten

Re: [SQL] Aggregate and join problem

2012-03-01 Thread Oliveiros d'Azevedo Cristina

Hi, Swärd,

As you didn't name your tables' columns I decided to call them col1, col2, etc.

I dunno if this will do what you want as it is completely untested code.

But, give it a try and see if it works and if it doesn't, tell me the error, 
and we'll continue from there.
You'll have to substitute my col1, col2, etc with your actual column names.

Best,
Oliveiros


SELECT query1.t1_id, t1.col2,t1.col3,t1.col4, query1.minimum
FROM (
SELECT t3.col2 as t1_id, MIN(t2.col2) as minimum
FROM Table_3 t3 
JOIN Table_2 t2
ON t3.col1 = t2.col1
GROUP BY t3.col2
) query1
JOIN  Table_1 t1
ON t1.col1 = query1.t1_id

  - Original Message - 
  From: Swärd Mårten 
  To: [email protected] 
  Sent: Thursday, March 01, 2012 10:30 AM
  Subject: [SQL] Aggregate and join problem


  Hi folks

  I have some troubles to create a SQL-query and my hope is that someone of you 
could help me with this..

  It's somewhat difficult to explain what I want to do but I'll give it a try 
and see if you can understand the problem.. Ahh fuck this.. It's almost 
imposible to explain.. J I don't understand what it mys self after I have 
written it.. Read and see if you understand.. J 

   

  I have three tables:

   

  Table_1:

  A table with meta data for areas (names, geometries and so..). Every area has 
a unique id.

  Example:

  101 | 'small area' | 'area name' | geom 

  102 | 'small area' | 'area name' | geom.

  103 | 'small area' | 'area name' | geom.

  104 | 'LARGE area' | 'area name' | geom

   

  Table 2.

  A table with values for some smaller areas. Contains a reference to an id in 
table1 and a value

  Example:

  101 | 12.5

  102 | 5.5

  103 | 6.5

   

   

  Table_3:

  A cross reference table with id:s for witch areas are connected to each 
other. Eg. What smaller areas that's is inside a larger area.

  A larger area could have many smaller areas connected to it

  Contains a reference to table 1 for the smaller areas and a reference to 
table 1 for the larger area. 

  Example:

  101 | 104

  102 | 104

  103 | 104

   

   

  What I want to do is the following:

  The larger area should "inherit" the lowest value from the smaller areas that 
are connected to it.

   

  I want to be able to get all larger areas and let them have a value that is 
the lowest value from table 2.

  If you look at the example data I only want to get the larger area (104) from 
table 1 with a value from table 2 that is the lowest value of the  areas 
connected to id 104. The result would be: 104 | 'LARGE area' | 'area name' | 
geom. | (value from table 2 id 102)

   

  Best regards, Mårten

Re: [SQL] Aggregate and join problem

2012-03-01 Thread Swärd Mårten

It worked like a charm! Many thanks for that great solution!
Best regards, Mårten

Från: Oliveiros d'Azevedo Cristina [mailto:[email protected]]
Skickat: den 1 mars 2012 11:49
Till: Swärd Mårten; [email protected]
Ämne: Re: [SQL] Aggregate and join problem

Hi, Swärd,

As you didn't name your tables' columns I decided to call them col1, col2, etc.

I dunno if this will do what you want as it is completely untested code.

But, give it a try and see if it works and if it doesn't, tell me the error, 
and we'll continue from there.
You'll have to substitute my col1, col2, etc with your actual column names.

Best,
Oliveiros


SELECT query1.t1_id, t1.col2,t1.col3,t1.col4, query1.minimum
FROM (
SELECT t3.col2 as t1_id, MIN(t2.col2) as minimum
FROM Table_3 t3
JOIN Table_2 t2
ON t3.col1 = t2.col1
GROUP BY t3.col2
) query1
JOIN  Table_1 t1
ON t1.col1 = query1.t1_id

- Original Message -
From: Swärd Mårten
To: [email protected]
Sent: Thursday, March 01, 2012 10:30 AM
Subject: [SQL] Aggregate and join problem

Hi folks
I have some troubles to create a SQL-query and my hope is that someone of you 
could help me with this..
It's somewhat difficult to explain what I want to do but I'll give it a try and 
see if you can understand the problem.. Ahh fuck this.. It's almost imposible 
to explain.. :) I don't understand what it mys self after I have written it.. 
Read and see if you understand.. :)

I have three tables:

Table_1:
A table with meta data for areas (names, geometries and so..). Every area has a 
unique id.
Example:
101 | 'small area' | 'area name' | geom
102 | 'small area' | 'area name' | geom.
103 | 'small area' | 'area name' | geom.
104 | 'LARGE area' | 'area name' | geom

Table 2.
A table with values for some smaller areas. Contains a reference to an id in 
table1 and a value
Example:
101 | 12.5
102 | 5.5
103 | 6.5


Table_3:
A cross reference table with id:s for witch areas are connected to each other. 
Eg. What smaller areas that's is inside a larger area.
A larger area could have many smaller areas connected to it
Contains a reference to table 1 for the smaller areas and a reference to table 
1 for the larger area.
Example:
101 | 104
102 | 104
103 | 104


What I want to do is the following:
The larger area should "inherit" the lowest value from the smaller areas that 
are connected to it.

I want to be able to get all larger areas and let them have a value that is the 
lowest value from table 2.
If you look at the example data I only want to get the larger area (104) from 
table 1 with a value from table 2 that is the lowest value of the  areas 
connected to id 104. The result would be: 104 | 'LARGE area' | 'area name' | 
geom. | (value from table 2 id 102)

Best regards, Mårten

Re: [SQL] Aggregate and join problem

2012-03-01 Thread Oliveiros d'Azevedo Cristina

Great to Hear!

Best,
Oliver
  - Original Message - 
  From: Swärd Mårten 
  To: Oliveiros d'Azevedo Cristina ; [email protected] 
  Sent: Thursday, March 01, 2012 11:01 AM
  Subject: Re: [SQL] Aggregate and join problem

  It worked like a charm! Many thanks for that great solution!

  Best regards, Mårten

  Från: Oliveiros d'Azevedo Cristina [mailto:[email protected]] 
  Skickat: den 1 mars 2012 11:49
  Till: Swärd Mårten; [email protected]
  Ämne: Re: [SQL] Aggregate and join problem

  Hi, Swärd,

  As you didn't name your tables' columns I decided to call them col1, col2, 
etc.

  I dunno if this will do what you want as it is completely untested code.

  But, give it a try and see if it works and if it doesn't, tell me the error, 
and we'll continue from there.

  You'll have to substitute my col1, col2, etc with your actual column names.

  Best,

  Oliveiros

  SELECT query1.t1_id, t1.col2,t1.col3,t1.col4, query1.minimum

  FROM (

  SELECT t3.col2 as t1_id, MIN(t2.col2) as minimum

  FROM Table_3 t3 

  JOIN Table_2 t2

  ON t3.col1 = t2.col1

  GROUP BY t3.col2

  ) query1

  JOIN  Table_1 t1

  ON t1.col1 = query1.t1_id

- Original Message - 

From: Swärd Mårten 

To: [email protected] 

Sent: Thursday, March 01, 2012 10:30 AM

Subject: [SQL] Aggregate and join problem

Hi folks

I have some troubles to create a SQL-query and my hope is that someone of 
you could help me with this..

It's somewhat difficult to explain what I want to do but I'll give it a try 
and see if you can understand the problem.. Ahh fuck this.. It's almost 
imposible to explain.. J I don't understand what it mys self after I have 
written it.. Read and see if you understand.. J 

I have three tables:

Table_1:

A table with meta data for areas (names, geometries and so..). Every area 
has a unique id.

Example:

101 | 'small area' | 'area name' | geom 

102 | 'small area' | 'area name' | geom.

103 | 'small area' | 'area name' | geom.

104 | 'LARGE area' | 'area name' | geom

Table 2.

A table with values for some smaller areas. Contains a reference to an id 
in table1 and a value

Example:

101 | 12.5

102 | 5.5

103 | 6.5

Table_3:

A cross reference table with id:s for witch areas are connected to each 
other. Eg. What smaller areas that's is inside a larger area.

A larger area could have many smaller areas connected to it

Contains a reference to table 1 for the smaller areas and a reference to 
table 1 for the larger area. 

Example:

101 | 104

102 | 104

103 | 104

What I want to do is the following:

The larger area should "inherit" the lowest value from the smaller areas 
that are connected to it.

I want to be able to get all larger areas and let them have a value that is 
the lowest value from table 2.

If you look at the example data I only want to get the larger area (104) 
from table 1 with a value from table 2 that is the lowest value of the  areas 
connected to id 104. The result would be: 104 | 'LARGE area' | 'area name' | 
geom. | (value from table 2 id 102)

Best regards, Mårten

[SQL] Change Ownership Recursively

2012-03-01 Thread Carlos Mennens

I have a database that I must assign ownership to a new role. I want
this new role to own the entire database and all of it's tables,
views, triggers, & all. When I run the ALTER DATABASE command below,
it only changes the database role but the tables are all still owned
by the previous role. Is there a way I can assign the 27 tables to
Lauren rather than doing the command one by one for each table?

postgres=# ALTER DATABASE iamunix OWNER TO lauren;
ALTER DATABASE
postgres=# \l
  List of databases
   Name|  Owner   | Encoding |   Collate   |Ctype|
Access privileges
---+--+--+-+-+---
  iamunix   | lauren   | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

All tables still owned by Carlos:

iamunix=# \d
   List of relations
 Schema |   Name   |   Type   | Owner
+--+--+
 public | dept | table| carlos
 public | dept_id_seq  | sequence | carlos
 public | employees| table| carlos
 public | employees_id_seq | sequence | carlos
 public | manager_lookup   | view | carlos
 public | managers | table| carlos
 public | managers_id_seq  | sequence | carlos

**PS**
I did do a Google search for "PostgreSQL 9.1 change ownership
recursively" but either couldn't find what I was looking for or
missed it.

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Change Ownership Recursively

2012-03-01 Thread Carlos Mennens

On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang
 wrote:
> Hi
> You can try this command "REASSIGN OWNED BY  TO ..." like this:
> REASSIGN OWNED BY previous_role TO new_role;
>  DROP OWNED previous_role;

I did as follows:

iamunix=# \c postgres
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
You are now connected to database "postgres" as user "carlos".
postgres=# REASSIGN OWNED BY carlos TO lauren;
REASSIGN OWNED
postgres=# DROP OWNED BY carlos;
DROP OWNED

iamunix=# \d
   List of relations
 Schema |   Name   |   Type   | Owner
+--+--+
 public | dept | table| carlos
 public | dept_id_seq  | sequence | carlos
 public | employees| table| carlos
 public | employees_id_seq | sequence | carlos
 public | manager_lookup   | view | carlos
 public | managers | table| carlos
 public | managers_id_seq  | sequence | carlos

That didn't work for some reason but mostly because I don't follow the
concept of what's being done. I've now since changed the database role
owner back to Carlos so now 'Carlos' owns both the database and all of
it's tables. Can we start fresh and assume I just got the request to
change the specified database and all of it's tables, sequences,
views, & triggers to Lauren?

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Natural sort order

2012-03-01 Thread F. BROUARD / SQLpro

The fastest way is to create a ref table with all possible entries, 
ordered with an additionnal numerical column, indexing it and make a 
join from your table to this ref table.


A +

Le 17/12/2011 11:33, Richard Klingler a écrit :

Morning...

What is the fastest way to achieve natural ordering from queries?

I found a function at:
http://2kan.tumblr.com/post/361326656/postgres-natural-ordering

But it increases the query time from around 0.4msecs to 74msecs...
Might be not much if occasional queries are made..but I use it for
building
up a hierarchical tree menu in a web application where every msecs
counts (o;


cheers
richard





--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*** http://www.sqlspot.com *


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Change Ownership Recursively

2012-03-01 Thread Adrian Klaver


On 03/01/2012 09:04 AM, Carlos Mennens wrote:

On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang
  wrote:

Hi
You can try this command "REASSIGN OWNED BY  TO ..." like this:
REASSIGN OWNED BY previous_role TO new_role;
  DROP OWNED previous_role;


I did as follows:

iamunix=# \c postgres
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
You are now connected to database "postgres" as user "carlos".


You are working on the database 'postgres' not 'iamunix'. REASSIGN works 
on the current database only. So you just REASSIGNED any objects in 
postgres.



postgres=# REASSIGN OWNED BY carlos TO lauren;
REASSIGN OWNED
postgres=# DROP OWNED BY carlos;
DROP OWNED

iamunix=# \d
List of relations
  Schema |   Name   |   Type   | Owner
+--+--+
  public | dept | table| carlos
  public | dept_id_seq  | sequence | carlos
  public | employees| table| carlos
  public | employees_id_seq | sequence | carlos
  public | manager_lookup   | view | carlos
  public | managers | table| carlos
  public | managers_id_seq  | sequence | carlos


Change into iamunix and do the REASSIGN.



That didn't work for some reason but mostly because I don't follow the
concept of what's being done. I've now since changed the database role
owner back to Carlos so now 'Carlos' owns both the database and all of
it's tables. Can we start fresh and assume I just got the request to
change the specified database and all of it's tables, sequences,
views,&  triggers to Lauren?


See above. For future reference including the Postgres version would be 
helpful. This area ownership/grants/etc has undergone a lot of changes 
over the various versions.







--
Adrian Klaver
[email protected]

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Change Ownership Recursively

2012-03-01 Thread Carlos Mennens

I changed to the suggested database which is owned by 'Carlos' and did
as instructed. Everything worked fine. Thank you!

On Thu, Mar 1, 2012 at 11:23 AM, Carlos Mennens
 wrote:
> I did do a Google search for "PostgreSQL 9.1 change ownership
> recursively" but either couldn't find what I was looking for or
> missed it.

On Thu, Mar 1, 2012 at 1:36 PM, Adrian Klaver  wrote:
> For future reference including the Postgres version would be
> helpful. This area ownership/grants/etc has undergone a lot of changes over
> the various versions.

I specified above I was using 9.1 PostgreSQL.

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Change Ownership Recursively

2012-03-01 Thread Adrian Klaver


On 03/01/2012 11:37 AM, Carlos Mennens wrote:

I changed to the suggested database which is owned by 'Carlos' and did
as instructed. Everything worked fine. Thank you!


In your previous post my guess is this:

iamunix=# \c postgres

was really meant to be:

iamunix=# \c - postgres

The first changes to database postgres as current user, the second
changes the user while remaining on the current database.



On Thu, Mar 1, 2012 at 11:23 AM, Carlos Mennens
  wrote:

I did do a Google search for "PostgreSQL 9.1 change ownership
recursively" but either couldn't find what I was looking for or
missed it.


On Thu, Mar 1, 2012 at 1:36 PM, Adrian Klaver  wrote:

For future reference including the Postgres version would be
helpful. This area ownership/grants/etc has undergone a lot of changes over
the various versions.


I specified above I was using 9.1 PostgreSQL.


Oops, my mistake, I never got to the PS:(


--
Adrian Klaver
[email protected]

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] No sort with except

Re: [SQL] No sort with except

Re: [SQL] No sort with except

Re: [SQL] No sort with except

Re: [SQL] No sort with except

[SQL] Aggregate and join problem

Re: [SQL] Aggregate and join problem

Re: [SQL] Aggregate and join problem

Re: [SQL] Aggregate and join problem

[SQL] Change Ownership Recursively

Re: [SQL] Change Ownership Recursively

Re: [SQL] Natural sort order

Re: [SQL] Change Ownership Recursively

Re: [SQL] Change Ownership Recursively

Re: [SQL] Change Ownership Recursively

15 matches

Site Navigation

Mail list logo

Footer information