pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDetermining correct table order for insert or drop statements to satisfy foreign keys
4+ messages / 2 participants
[nested] [flat]
* Determining correct table order for insert or drop statements to satisfy foreign keys
@ 2006-11-02 19:43 Peter Hanson <lists@pkhanson.com>
2006-11-02 22:58 ` Re: Determining correct table order for insert or drop statements to satisfy foreign keys Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 4+ messages in thread
From: Peter Hanson @ 2006-11-02 19:43 UTC (permalink / raw)
To: pgsql-sql
Hello,
First, sorry if this has already been covered, though I didn't find anything
similar in the archives. Also sorry if this is the wrong list for this type
of question, though it seemed to be the most relevant list for this question.
On to the question:
I'm writing a simple import script that reads in an excel file that a customer
fills out. Each worksheet represents a different table and the tables' fields
are columns in each worksheet.
The tables represented in the file are variable, I have no idea what table
combinations will be in the excel file when it's sent to the script.
What I need is a way for the script to determine what order it should process
the worksheets in the excel file.
For example, you have this table structure (each table would be a worksheet):
PERSON (1) <--> (many) ORDER (1) <--> (many) ORDER_ITEM
Obviously, the people would have to be inserted first, then the orders, and
finally the items, to keep the foreign keys between the tables happy.
Is there a mechanism in PostgreSQL that would tell me this order? Or is it
something that would have to be assembled from analyzing the schema? Anybody
needed to perform an operation like this before? Any help/direction would be
greatly appreciated.
Thanks for your time and help,
Pete Hanson
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Determining correct table order for insert or drop statements to satisfy foreign keys
2006-11-02 19:43 Determining correct table order for insert or drop statements to satisfy foreign keys Peter Hanson <lists@pkhanson.com>
@ 2006-11-02 22:58 ` Aaron Bono <postgresql@aranya.com>
2006-11-02 23:16 ` Re: Determining correct table order for insert or drop statements to satisfy foreign keys Peter Hanson <lists@pkhanson.com>
0 siblings, 1 reply; 4+ messages in thread
From: Aaron Bono @ 2006-11-02 22:58 UTC (permalink / raw)
To: lists@pkhanson.com; +Cc: pgsql-sql
On 11/2/06, Peter Hanson <lists@pkhanson.com> wrote:
>
> Hello,
>
> First, sorry if this has already been covered, though I didn't find
> anything
> similar in the archives. Also sorry if this is the wrong list for this
> type
> of question, though it seemed to be the most relevant list for this
> question.
>
> On to the question:
>
> I'm writing a simple import script that reads in an excel file that a
> customer
> fills out. Each worksheet represents a different table and the tables'
> fields
> are columns in each worksheet.
>
> The tables represented in the file are variable, I have no idea what table
> combinations will be in the excel file when it's sent to the script.
>
> What I need is a way for the script to determine what order it should
> process
> the worksheets in the excel file.
>
> For example, you have this table structure (each table would be a
> worksheet):
>
> PERSON (1) <--> (many) ORDER (1) <--> (many) ORDER_ITEM
>
> Obviously, the people would have to be inserted first, then the orders,
> and
> finally the items, to keep the foreign keys between the tables happy.
>
> Is there a mechanism in PostgreSQL that would tell me this order? Or is
> it
> something that would have to be assembled from analyzing the
> schema? Anybody
> needed to perform an operation like this before? Any help/direction would
> be
> greatly appreciated.
>
What programming language are you using? Many languages provide mechanisms
to ask the database for metadata about tables including primary keys,
indexes, data types and foreign keys. That is one option for you.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Determining correct table order for insert or drop statements to satisfy foreign keys
2006-11-02 19:43 Determining correct table order for insert or drop statements to satisfy foreign keys Peter Hanson <lists@pkhanson.com>
2006-11-02 22:58 ` Re: Determining correct table order for insert or drop statements to satisfy foreign keys Aaron Bono <postgresql@aranya.com>
@ 2006-11-02 23:16 ` Peter Hanson <lists@pkhanson.com>
2006-11-03 18:04 ` Re: Determining correct table order for insert or drop statements to satisfy foreign keys Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 4+ messages in thread
From: Peter Hanson @ 2006-11-02 23:16 UTC (permalink / raw)
To: pgsql-sql
Hi Aaron,
I'm using PHP5 as the programming language.
Thanks,
Pete
On Thursday 02 November 2006 16:58, Aaron Bono wrote:
> On 11/2/06, Peter Hanson <lists@pkhanson.com> wrote:
> > Hello,
> >
> > First, sorry if this has already been covered, though I didn't find
> > anything
> > similar in the archives. Also sorry if this is the wrong list for this
> > type
> > of question, though it seemed to be the most relevant list for this
> > question.
> >
> > On to the question:
> >
> > I'm writing a simple import script that reads in an excel file that a
> > customer
> > fills out. Each worksheet represents a different table and the tables'
> > fields
> > are columns in each worksheet.
> >
> > The tables represented in the file are variable, I have no idea what
> > table combinations will be in the excel file when it's sent to the
> > script.
> >
> > What I need is a way for the script to determine what order it should
> > process
> > the worksheets in the excel file.
> >
> > For example, you have this table structure (each table would be a
> > worksheet):
> >
> > PERSON (1) <--> (many) ORDER (1) <--> (many) ORDER_ITEM
> >
> > Obviously, the people would have to be inserted first, then the orders,
> > and
> > finally the items, to keep the foreign keys between the tables happy.
> >
> > Is there a mechanism in PostgreSQL that would tell me this order? Or is
> > it
> > something that would have to be assembled from analyzing the
> > schema? Anybody
> > needed to perform an operation like this before? Any help/direction would
> > be
> > greatly appreciated.
>
> What programming language are you using? Many languages provide mechanisms
> to ask the database for metadata about tables including primary keys,
> indexes, data types and foreign keys. That is one option for you.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Determining correct table order for insert or drop statements to satisfy foreign keys
2006-11-02 19:43 Determining correct table order for insert or drop statements to satisfy foreign keys Peter Hanson <lists@pkhanson.com>
2006-11-02 22:58 ` Re: Determining correct table order for insert or drop statements to satisfy foreign keys Aaron Bono <postgresql@aranya.com>
2006-11-02 23:16 ` Re: Determining correct table order for insert or drop statements to satisfy foreign keys Peter Hanson <lists@pkhanson.com>
@ 2006-11-03 18:04 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-11-03 18:04 UTC (permalink / raw)
To: lists@pkhanson.com; +Cc: pgsql-sql
On 11/2/06, Peter Hanson <lists@pkhanson.com> wrote:
>
> Hi Aaron,
>
> I'm using PHP5 as the programming language.
Unfortunately it looks like PHP has very limited support for meta data:
http://us2.php.net/manual/en/function.pg-meta-data.php
Check this out:
http://www.postgresql.org/docs/8.0/interactive/functions-info.html
http://archives.postgresql.org/pgsql-admin/2006-08/msg00147.php
See if that points you in the right direction.
On Thursday 02 November 2006 16:58, Aaron Bono wrote:
> > On 11/2/06, Peter Hanson <lists@pkhanson.com> wrote:
> > > Hello,
> > >
> > > First, sorry if this has already been covered, though I didn't find
> > > anything
> > > similar in the archives. Also sorry if this is the wrong list for this
> > > type
> > > of question, though it seemed to be the most relevant list for this
> > > question.
> > >
> > > On to the question:
> > >
> > > I'm writing a simple import script that reads in an excel file that a
> > > customer
> > > fills out. Each worksheet represents a different table and the tables'
> > > fields
> > > are columns in each worksheet.
> > >
> > > The tables represented in the file are variable, I have no idea what
> > > table combinations will be in the excel file when it's sent to the
> > > script.
> > >
> > > What I need is a way for the script to determine what order it should
> > > process
> > > the worksheets in the excel file.
> > >
> > > For example, you have this table structure (each table would be a
> > > worksheet):
> > >
> > > PERSON (1) <--> (many) ORDER (1) <--> (many) ORDER_ITEM
> > >
> > > Obviously, the people would have to be inserted first, then the
> orders,
> > > and
> > > finally the items, to keep the foreign keys between the tables happy.
> > >
> > > Is there a mechanism in PostgreSQL that would tell me this order? Or
> is
> > > it
> > > something that would have to be assembled from analyzing the
> > > schema? Anybody
> > > needed to perform an operation like this before? Any help/direction
> would
> > > be
> > > greatly appreciated.
> >
> > What programming language are you using? Many languages provide
> mechanisms
> > to ask the database for metadata about tables including primary keys,
> > indexes, data types and foreign keys. That is one option for you.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-11-03 18:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-02 19:43 Determining correct table order for insert or drop statements to satisfy foreign keys Peter Hanson <lists@pkhanson.com>
2006-11-02 22:58 ` Aaron Bono <postgresql@aranya.com>
2006-11-02 23:16 ` Peter Hanson <lists@pkhanson.com>
2006-11-03 18:04 ` Aaron Bono <postgresql@aranya.com>
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