Re: List tables in reverse dependancy order - Mailing list pgsql-general

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.)
>
>