Re: Foreign Key 'walker'?

Hello

I used this code

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;


sorry, identifiers are in czech

regards
Pavel Stehule

2008/11/18 Erwin Moller <erwin@darwine.nl>:
> Shane Ambler schreef:
>>
>> ries van Twisk wrote:
>>>
>>> On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote:
>>>
>>>> Hi group,
>>>>
>>>> Considering following (simplified) example:
>>
>> <snip>
>>>>
>>>> Suppose I want to delete a record in tblnr1.
>>>> Does Postgres has some command/procedure/function to list tables that
>>>> have FK constraints on that table (tblnr1)
>>
>> The data you are looking for is stored in the system catalogs.
>> http://www.postgresql.org/docs/8.3/interactive/catalogs.html
>> You should be able to come up with some SELECT's to get what you want.
>>
>>>> and lists also the tables that have a FK constraint on tables that have
>>>> a FK constraint on the first? etc.
>>>> So I would like some kind of FK 'walker'.
>>>>
>>>> I want this because:
>>>> 1) I hate DELETE CASCADE because I am chicken (So I use a script to
>>>> delete all related records in the right order in a transaction)
>>
>> That isn't being chicken it is being silly (or is that just stubborn ;).
>> PostgreSQL is designed to delete related records that you tell it to
>> delete. Let it do what it is suppose to do.
>
> Hi,
>
> No, that is not the kind of chicken I was talking about. ;-)
> My chicken is more along these lines:
> I often have some tables to which everything is related (eg tblcourse that
> contains everything belonging to a certain course).
> I don't want to make a single simple mistake that if I accidentally delete
> an entry there, I lose all underlying data via the CASCADE.
> That is why I decided never to use CASCADE, and simply do it by myself.
> No big deal except that I have to find out the related tables.
> I rather have a FK constraint violation error than an empty DB.
>
> Hence my question.
> I am not afraid that Postgres will screw up somehow.
> That actually NEVER happened in all the years I am using it. Try that with
> MSSQL or MySQL. I love Postgres. ;-)
>
>>
>>>> 2) I have a lot of tables and am afraid I miss some. And I am also a bit
>>>> lazy .-)
>>
>> If your lazy why do all this manual work when you can leave it automated?
>> It won't miss a related record after you tell it to cascade delete. This is
>> an old well tested feature that you can rely on.
>
> I described above.
>>
>>
>> That probably all sounds more aggressive than it should. Not having a
>> strong dig at you but I do want to emphasise the fact that you shouldn't
>> waste your time doing manually what the software is designed to do.
>
> No problem at all.
> I totally agree with you.
> I only have this fear I screw up (not Postgresql) if I use CASCADE and
> accidentally delete a 'high' record in the chain.
>
> Regards,
> Erwin Moller
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>