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 <[EMAIL PROTECTED]>:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
