[SQL] Order of "WITH RECURSIVE" output

[Andreas Joseph Krogh]

Hi all.
Take this example from src/test/regress/sql/with.sql:

WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
(
-- non recursive term
SELECT 1, * FROM department WHERE name = 'A'

UNION ALL

-- recursive term
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;

 level | id | parent_department | name
---++---+--
 1 |  1 | 0 | A
 2 |  2 | 1 | B
 3 |  3 | 2 | C
 3 |  4 | 2 | D
 4 |  6 | 4 | F
(5 rows)

If I omit "ORDER BY", is the output *guaranteed* (according to some standard) 
to be ordered in "hierarchy"-order, meaning each parent-department is always 
output before its respective child, or do I have to calculate a "level" column 
(like in the example above) and specify "ORDER BY LEVEL" to be 100%?

I'm using WITH RECURSIVE as sub-queries to build up arrays of parents in each 
output-row and would like as little overhead as possible and hence avoid 
unnecessary sorting.

-- 
Andreas Joseph Krogh 
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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

Re: [SQL] Order of "WITH RECURSIVE" output

[Tom Lane]

Andreas Joseph Krogh  writes:
> If I omit "ORDER BY", is the output *guaranteed* (according to some
> standard) to be ordered

No.  It's not necessary to read any other details to answer that ...

regards, tom lane

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

Re: [SQL] Order of "WITH RECURSIVE" output

[Andreas Joseph Krogh]

On Tuesday 07 July 2009 03:55:35 am Tom Lane wrote:
> Andreas Joseph Krogh  writes:
> > If I omit "ORDER BY", is the output *guaranteed* (according to some
> > standard) to be ordered
> 
> No.  It's not necessary to read any other details to answer that ...
> 
>   regards, tom lane

Ok, thanks for prompt reply.

-- 
Andreas Joseph Krogh 
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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

[SQL] Recording how a table is used

[Daniel Gordon]

I'm trying to record the results of a select statement into a separate
table.  I need the information selected, the column it was stored in, the
table it was stored in, and the query that selected it.  Everything I've
read so far says that triggers can't trigger from select statements, and
they don't have a variable that stores the query it triggered from.  I would
be happy with partial solutions since my other option is a complicated perl
script that has to parse the query statement to figure out where it is
looking for the information

Also, my next step is to do the same for as many types of statements as
possible, so if anyone knows how to retrieve the insert, update, or delete
statement sent to the database from inside the trigger, that would be useful
as well.

Here is the table I'm trying to fill, in case it is useful

create table sql_query_data_log (
 id serial,
 create_ time timestamp DEFAULT now(),
 query varchar,
 table text,
 column text,
 data varchar
 );

Re: [SQL] fsync and Windows XP

[Rainer Bauer]

"Jon Norman" schrieb:

>What is the trick for getting the fsync feature to work with Windows XP.
> I've turned off disk caching and set fsync = on in my postgresql.conf,
> but records are still not immediately written to disk.

Check out the wal_sync_method parameter:


Rainer

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

[SQL] Sequences

[Andre Rothe]

Hi!

Where are stored the sequence information? How I can query the
properties of a sequence like increment, max/min value, cache?
I'm looking for a table like user_sequences in Oracle, where I
can query all of my sequences.

Thanks
Andre

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

[SQL] Best way to simulate Booleans

[Peter Headland]

I know, I know, PostgreSQL has Booleans that work very nicely.
Unfortunately, I have to create a schema that will work on Oracle as
well as PostgreSQL, by which I mean that a single set of Java/JDBC code
has to work with both databases. I have an XML meta-schema that enables
me to generate appropriate DDL; that handles all the INTEGER vs.
NUMBER(m,n) stuff. But Oracle simply has no Booleans, so I will have to
resort to some more or less ugly alternative. I am hoping that others
here have had to deal with this and can suggest an approach that will be
minimally loathsome.

God I hate Oracle...

-- 
Peter Headland
Architect - e.Reports
Actuate Corporation

Re: [SQL] Sequences

[Andre Rothe]

Thanks, I have used

SELECT sequence_name FROM information_schema.sequences WHERE 
sequence_catalog=? AND sequence_schema=?


and then I can use

select * from 

It seems to work :-)
Andre

Chris Browne wrote:

Andre Rothe  writes:

Where are stored the sequence information? How I can query the
properties of a sequence like increment, max/min value, cache?
I'm looking for a table like user_sequences in Oracle, where I
can query all of my sequences.


cbbrowne=# create sequence foo;
CREATE SEQUENCE
cbbrowne=# select * from foo;
 sequence_name | last_value | increment_by |  max_value  | min_value | 
cache_value | log_cnt | is_cycled | is_called
---++--+-+---+-+-+---+---
 foo   |  1 |1 | 9223372036854775807 | 1 |  
 1 |   1 | f | f
(1 row)

Each sequence is effectively a relation.


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

Re: [SQL] Best way to simulate Booleans

[Scott Marlowe]

On Mon, Jul 6, 2009 at 7:22 PM, Peter Headland wrote:
> I know, I know, PostgreSQL has Booleans that work very nicely.
> Unfortunately, I have to create a schema that will work on Oracle as well as
> PostgreSQL, by which I mean that a single set of Java/JDBC code has to work
> with both databases. I have an XML meta-schema that enables me to generate
> appropriate DDL; that handles all the INTEGER vs. NUMBER(m,n) stuff. But
> Oracle simply has no Booleans, so I will have to resort to some more or less
> ugly alternative. I am hoping that others here have had to deal with this
> and can suggest an approach that will be minimally loathsome.

The most transportable method would be to use either a char(1) or an
int with a check constraint.

mybool char(1) check (mybool in ('t','f'))
mybool int check (mybool >=0 and <=1)

Or something like that.

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