Re: Foreign Key 'walker'? - Mailing list pgsql-general

Hi Pavel,

Thanks for that.
But I already wrote a nice extension to my DB-class in PHP that uses
Thomas Kellerer's approach.
It was simple once you know how to retrieve the info from the
systemtables. :-)

Regards,
Erwin Moller


Pavel Stehule schreef:
> 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
>>
>>
>
>