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(at)darwine(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>