Thread: List tables in reverse dependancy order

I am not sure if this is the appropriate list -- please point me at the correct one if not.

I'm trying to create a procedure that would let me retrieve a list of tables and views in a database that will be used to control the order in which lookup data is created/loaded. So, much simplified, if table references table B, which in turn references table A, we want output to list table A, B and C in that order.

I'm sure that this exists -- the pg_dump command must use some similar algorithm to decide in which order to load tables, but I can't see to puzzle this out.

Can anyone provide me with some clues, appropriate RTFM references, etc. ?

TIA,

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

"Gregory Williamson" <Gregory.Williamson@digitalglobe.com> writes:
> I'm trying to create a procedure that would let me retrieve a list of =
> tables and views in a database that will be used to control the order in =
> which lookup data is created/loaded. So, much simplified, if table =
> references table B, which in turn references table A, we want output to =
> list table A, B and C in that order.

> I'm sure that this exists -- the pg_dump command must use some similar =
> algorithm to decide in which order to load tables, but I can't see to =
> puzzle this out.

pg_dump expends a fair amount of code on this problem; if you want to
handle the general case with circular references and so on, it's not
simple.  You could do worse than to run "pg_dump -s" and postprocess
its output.

If you are only interested in simpler cases then you might be able to
find a simpler solution.  For instance if you are only worried about
foreign-key linkages then looking into pg_constraint is much the
easiest way to find out about those.

            regards, tom lane

Thanks ... I was afraid it would as messy as it is; unfortunately Oracle seems to have a way to gather at least some of this in one (ugly) SQL command and I was hoping for some equivalent trick.

Greg W.
(apologies for top-posting -- limited mail reader)

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sat 8/4/2007 9:51 AM
To: Gregory Williamson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] List tables in reverse dependancy order

"Gregory Williamson" <Gregory.Williamson@digitalglobe.com> writes:
> I'm trying to create a procedure that would let me retrieve a list of =
> tables and views in a database that will be used to control the order in =
> which lookup data is created/loaded. So, much simplified, if table =
> references table B, which in turn references table A, we want output to =
> list table A, B and C in that order.

> I'm sure that this exists -- the pg_dump command must use some similar =
> algorithm to decide in which order to load tables, but I can't see to =
> puzzle this out.

pg_dump expends a fair amount of code on this problem; if you want to
handle the general case with circular references and so on, it's not
simple.  You could do worse than to run "pg_dump -s" and postprocess
its output.

If you are only interested in simpler cases then you might be able to
find a simpler solution.  For instance if you are only worried about
foreign-key linkages then looking into pg_constraint is much the
easiest way to find out about those.

                        regards, tom lane

Hello,

five years ago I used

CREATE OR REPLACE FUNCTION list_user_tables_sort_depend
  (owner VARCHAR, revers BOOLEAN) RETURNS SETOF VARCHAR AS '
DECLARE tabulky VARCHAR[]; i INTEGER; opakovat BOOLEAN = ''t'';
  pom VARCHAR; exportovano VARCHAR[] = ''{}''; r RECORD;
mohu_exportovat BOOLEAN;

BEGIN SELECT ARRAY(SELECT tablename FROM pg_tables WHERE tableowner =
owner) INTO tabulky;
  WHILE opakovat LOOP
    opakovat := ''f'';
    FOR i IN array_lower(tabulky,1) .. array_upper(tabulky,1) LOOP
      IF tabulky[i] <> '''' THEN
        mohu_exportovat := ''t'';
        FOR r IN SELECT t.relname AS z, x.relname AS nz FROM
pg_catalog.pg_constraint d
          INNER JOIN pg_catalog.pg_class t on t.oid = d.conrelid
          INNER JOIN pg_catalog.pg_class x on x.oid = d.confrelid
            WHERE d.contype = ''f'' AND t.relname = tabulky[i] LOOP
          IF NOT r.nz = ANY(exportovano) THEN
            mohu_exportovat := ''f'';
          END IF;
        END LOOP;
        IF mohu_exportovat THEN
          pom := tabulky[i];
          exportovano := exportovano || tabulky[i];
          opakovat := ''t''; tabulky[i] := '''';
        END IF;
      END IF;
    END LOOP;
  END LOOP;
  IF revers THEN
    FOR i IN REVERSE array_upper(exportovano,1) ..
array_lower(exportovano,1) LOOP
      RETURN NEXT exportovano[i];
    END LOOP;
  ELSE
    FOR i IN array_lower(exportovano,1) .. array_upper(exportovano,1) LOOP
      RETURN NEXT exportovano[i];
    END LOOP;
  END IF;
  RETURN;
END;
' LANGUAGE plpgsql;

I am sorry for czech variable names

Regards
Pavel Stehule


2007/8/2, Gregory Williamson <Gregory.Williamson@digitalglobe.com>:
>
>
>
> I am not sure if this is the appropriate list -- please point me at the
> correct one if not.
>
>  I'm trying to create a procedure that would let me retrieve a list of
> tables and views in a database that will be used to control the order in
> which lookup data is created/loaded. So, much simplified, if table
> references table B, which in turn references table A, we want output to list
> table A, B and C in that order.
>
>  I'm sure that this exists -- the pg_dump command must use some similar
> algorithm to decide in which order to load tables, but I can't see to puzzle
> this out.
>
>  Can anyone provide me with some clues, appropriate RTFM references, etc. ?
>
>  TIA,
>
>  Greg Williamson
>  Senior DBA
>  GlobeXplorer LLC, a DigitalGlobe company
>
>  Confidentiality Notice: This e-mail message, including any attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> and privileged information and must be protected in accordance with those
> provisions. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please contact the sender
> by reply e-mail and destroy all copies of the original message.
>
>  (My corporate masters made me say this.)
>
>