Re: List tables in reverse dependancy order

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(dot)Williamson(at)digitalglobe(dot)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.)
>
>