Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 21ED49FB296 for ; Fri, 3 Nov 2006 14:05:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 94252-03 for ; Fri, 3 Nov 2006 18:04:56 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 2F1B89FB28D for ; Fri, 3 Nov 2006 14:04:56 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so432545ugf for ; Fri, 03 Nov 2006 10:04:55 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=EnWHwlhdyjFhF17eJ2XAacf1Bozsfo/0b0Ih3bzjUm1SuEbuJ7Fh1iKjOEJkwMz7jeWJS6Jp7lJYUivJ/ScrJdNBL0W48mS3rMoITJ4fkbz4e5Q5YRIuv/1OuygbuIXxTLDbGBM39Jvc8uNQx48nnbUOywVAWFOACZbwunXJgY0= Received: by 10.78.136.9 with SMTP id j9mr3248658hud.1162577095064; Fri, 03 Nov 2006 10:04:55 -0800 (PST) Received: by 10.78.136.6 with HTTP; Fri, 3 Nov 2006 10:04:54 -0800 (PST) Message-ID: Date: Fri, 3 Nov 2006 12:04:54 -0600 From: "Aaron Bono" To: lists@pkhanson.com Subject: Re: Determining correct table order for insert or drop statements to satisfy foreign keys Cc: pgsql-sql@postgresql.org In-Reply-To: <200611021716.49044.lists@pkhanson.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_16815_1452662.1162577094946" References: <200611021343.31622.lists@pkhanson.com> <200611021716.49044.lists@pkhanson.com> X-Google-Sender-Auth: 26dbc1b89b6e1ef9 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/40 X-Sequence-Number: 26692 ------=_Part_16815_1452662.1162577094946 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/2/06, Peter Hanson 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 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 ================================================================== ------=_Part_16815_1452662.1162577094946 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
================================================================== ------=_Part_16815_1452662.1162577094946--