Re: [SQL] connectby documentation
Daniel Caune a écrit : > Wow, that was the quest for the Holy Grail! :-) Yes I understand. That kind of documentation for a contrib-addon-whatever for PostgreSQL can be tricky sometimes to find.. I just jump on that thread to place a reminder for all those wanting to implement trees in databases, just in case they are still thinking about howto do that. I wroted an article on that topic (in french only sorry : http://www.postgresqlfr.org/?q=node/142), where I compare id/parent_id, nested loops and Miguel Sofer's method. This method is explained on OpenACS forums (in english) http://openacs.org/forums/message-view?message_id=18365 The original work of Miguel Sofer (with a PostgreSQL implementation as an example) can be found here: http://www.utdt.edu/~mig/sql-trees/ Be sure to download the tar.gz. on the like "here"... and read his draft. I'm really convinced this method is the best so far. I used it in 3 different projects where I had to implement big trees structures on a table. They all still work with no problem of any kind. Just to let you know in case you missed that ;-) My 2 ¢ -- Jean-Paul Argudo www.Argudo.org www.PostgreSQLFr.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] connectby documentation
O Jean-Paul Argudo έγραψε στις Mar 14, 2006 :
> Daniel Caune a ιcrit :
> > Wow, that was the quest for the Holy Grail! :-)
>
> Yes I understand. That kind of documentation for a
> contrib-addon-whatever for PostgreSQL can be tricky sometimes to find..
>
>
> I just jump on that thread to place a reminder for all those wanting to
> implement trees in databases, just in case they are still thinking about
> howto do that.
>
> I wroted an article on that topic (in french only sorry :
> http://www.postgresqlfr.org/?q=node/142), where I compare id/parent_id,
> nested loops and Miguel Sofer's method.
>
> This method is explained on OpenACS forums (in english)
>
> http://openacs.org/forums/message-view?message_id=18365
>
> The original work of Miguel Sofer (with a PostgreSQL implementation as
> an example) can be found here:
>
> http://www.utdt.edu/~mig/sql-trees/
>
> Be sure to download the tar.gz. on the like "here"... and read his draft.
>
> I'm really convinced this method is the best so far. I used it in 3
> different projects where I had to implement big trees structures on a
> table. They all still work with no problem of any kind.
I agree, this genealogical approach is i think the most
intuitive/efficient, however this depends on the nature
of the intented operation types.
One implementation of this (i think) is the ltree contrib module.
Haven't worked with this tho.
What i actually did for my ultra demanding task (modeling inventory
maintenance of 709772 machinery items/parts etc... of ~ 40 vessels), was
smth of the type
defid | integer| not null default
nextval('public.machdefs_defid_seq'::text)
parents | integer[] |
description | text |
machtypeid | integer
..
where parents hold the path from the item's direct parent to its root
ancestor,
and tree queries are done with a help of a intarray index on parents
"machdefs_parents" gist (parents gist__intbig_ops)
>
> Just to let you know in case you missed that ;-)
>
> My 2 ’
>
> --
> Jean-Paul Argudo
> www.Argudo.org
> www.PostgreSQLFr.org
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
--
-Achilleus
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Copying a row within table
Assuming the sequence in foo is named foo_seq, you could do:
-- You could also select multiple rows here, e.g. foo_id>10, if desired.
create temp table foo_tmp as select * from foo where foo_id=2;
alter table foo_tmp add column tmp_seq int default nextval('foo_seq');
-- foo_tmp now *shares* the sequence.
insert into foo select * from foo_tmp;
drop table foo_tmp;
If there's any chance of concurrent update/insert/deletes to foo, you
might should wrap this in a (begin; stuff; commit) transaction.
-- George Young
On Tue, 14 Mar 2006 09:19:49 +0200
Aarni Ruuhimäki <[EMAIL PROTECTED]> threw this fish to the penguins:
>
> testing=# INSERT INTO foo (SELECT * FROM foo WHERE foo_id = 2);
> ERROR: duplicate key violates unique constraint "foo_pkey"
> testing=#
>
> testing=# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1, foo_2,
> foo_3 ... FROM message_table WHERE foo_id = 10);
> INSERT 717286 1
> testing=#
>
> Is there a fast way to copy all but not the PK column to a new row within the
> same table so that the new foo_id gets its value from the sequence ?
>
> TIA and BR,
>
> Aarni
>
> --
> Aarni Ruuhimäki
> --
> This is a bugfree broadcast to you
> from **Kmail**
> on **Fedora Core** linux system
> --
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] how to get the size of array?
Hello,
Is there a way that I can get the size of one array ?
For example, create table test (id varchar[]);
insert into test values('{}');
insert into test values('{1, 2, 3}');
I am looking for something like :
select sizeOf(id) as size from test;
so that I can get results like:
size
---
0
3
Thanks a lot,
Ying
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] how to get the size of array?
Emi Lu <[EMAIL PROTECTED]> schrieb: > Hello, > > Is there a way that I can get the size of one array ? Yes: http://www.postgresql.org/docs/8.1/interactive/functions-array.html HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Savepoint/Rollback in functions
I am running postgres 8.1.2 on both a windows and Linux platform. The primary method of managing business logic is through the use of functions. Our primary access to the database is by using PSQL (pg/psql). The problem that I am having is that we need to execute and implement "SAVEPOINT" and "ROLLBACK" functionality inside of FUNCTIONS. The "SAAVEPOINT/ROLLBACK" works fine interactively. At the present time the documentation does not seem to be definitive on the functionality of this, BUT, we placed "SAVEPOINT/ROLLBACK" commands in a test function and the function failed. Question 1: does pg/psql functions allow "SAVEPOINT/ROLLBACK" functionality? (if so how?) Question 2: if the answer to question 1 is "NO", what would be required to get this implemented in the standard product? Thanks in advance for any information and help. Scott. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Savepoint/Rollback in functions
Scott Petersen wrote: Hi, > Question 1: does pg/psql functions allow "SAVEPOINT/ROLLBACK" > functionality? (if so how?) Yes. However, you cannot use that syntax directly. You rather use it by establishing EXCEPTION clauses in BEGIN/END blocks. Upon entering any BEGIN/END block which has an EXCEPTION clause, an implicit SAVEPOINT is executed. If any exception (read: error) is found while executing the block, the savepoint will be automatically rolled back and control passed to the EXCEPTION block. HTH, -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Copying a row within table
On Mar 14, 2006, at 2:19 AM, Aarni Ruuhimäki wrote:
testing=# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1,
foo_2,
foo_3 ... FROM message_table WHERE foo_id = 10);
INSERT 717286 1
testing=#
Is there a fast way to copy all but not the PK column to a new row
within the
same table so that the new foo_id gets its value from the sequence ?
Here is an example using a plpgsql function:
create or replace function test_duplicate (p_id integer)
returns integer as $$
declare
tt test%rowtype;
begin
select into tt * from test where id = p_id;
tt.id := nextval(pg_get_serial_sequence('test', 'id'));
insert into test values (tt.*);
return tt.id;
end;
$$ language plpgsql;
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
