pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

SQL tree duplication
2+ messages / 2 participants
[nested] [flat]

* SQL tree duplication
@ 2008-01-24 09:42  Philippe Lang <philippe.lang@attiksystem.ch>
  0 siblings, 1 reply; 2+ messages in thread

From: Philippe Lang @ 2008-01-24 09:42 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I have the following kind of sql structure, in 3 levels:

-----------------------
create table documents (
  id serial,
  name varchar(50),
  primary key (id)
);

create table lines (
  id serial,
  name varchar(50),
  document_id integer,
  primary key (id),
  foreign key (document_id) references documents (id)
);

create table line_details (
  id serial,
  name varchar(50),
  line_id integer,
  primary key (id),
  foreign key (line_id) references lines (id)
);
-----------------------

I'd like to be able to "duplicate" a document, with all of its lines and
line details.

Is there any easy way to do that with Postgresql? The only solution I
can think of at the moment is to loop through all lines and line
details, and replace foreign keys properly with values fetch with
"currval". It should work just fine, but I was wondering if some
advanced features of Postgresql could help in this situation.

Regards,

Philippe Lang



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SQL tree duplication
@ 2008-01-24 14:09  tv@fuzzy.cz
  parent: Philippe Lang <philippe.lang@attiksystem.ch>
  0 siblings, 0 replies; 2+ messages in thread

From: tv@fuzzy.cz @ 2008-01-24 14:09 UTC (permalink / raw)
  To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-sql

> Hi,
>
> I have the following kind of sql structure, in 3 levels:
>
> -----------------------
> create table documents (
>   id serial,
>   name varchar(50),
>   primary key (id)
> );
>
> create table lines (
>   id serial,
>   name varchar(50),
>   document_id integer,
>   primary key (id),
>   foreign key (document_id) references documents (id)
> );
>
> create table line_details (
>   id serial,
>   name varchar(50),
>   line_id integer,
>   primary key (id),
>   foreign key (line_id) references lines (id)
> );
> -----------------------
>
> I'd like to be able to "duplicate" a document, with all of its lines and
> line details.
>
> Is there any easy way to do that with Postgresql? The only solution I
> can think of at the moment is to loop through all lines and line
> details, and replace foreign keys properly with values fetch with
> "currval". It should work just fine, but I was wondering if some
> advanced features of Postgresql could help in this situation.

You will have to do that in 3 steps (one for each table), but looping may
not be necessary - just use INSERT ... SELECT ... syntax. Something like

INSERT INTO Lines SELECT FROM Lines WHERE document_id = OLD_ID;

But it depends on primary keys in the Lines and Line_details tables - if
the primary keys are composed (and the document_id is part of them) then
there is no problem with duplicities. Otherwise you'll have to solve it
somehow, and looping may be necessary.

Tomas




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2008-01-24 14:09 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-24 09:42 SQL tree duplication Philippe Lang <philippe.lang@attiksystem.ch>
2008-01-24 14:09 ` tv@fuzzy.cz

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox