Re: functional dependency tool

Hello

I used script:

#!/bin/bash
psql intra <<EOF
CREATE OR REPLACE FUNCTION list_user_tables_sort_depend
(owner VARCHAR, revers BOOLEAN) RETURNS SETOF VARCHAR AS '
DECLARE tables VARCHAR[]; i INTEGER; repeat BOOLEAN = ''t'';
aux VARCHAR; exported VARCHAR[] = ''{}''; r RECORD; can_export BOOLEAN;

BEGIN SELECT ARRAY(SELECT tablename FROM pg_tables WHERE tableowner =
owner) INTO tables;
WHILE repeat LOOP
repeat := ''f'';
FOR i IN array_lower(tables,1) .. array_upper(tables,1) LOOP
IF tables[i] <> '''' THEN
can_export := ''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 = tables[i] LOOP
IF NOT r.nz = ANY(exported) THEN
can_export := ''f'';
END IF;
END LOOP;
IF can_export THEN
aux := tables[i];
exported := exported || tables[i];
repeat := ''t''; tables[i] := '''';
END IF;
END IF;
END LOOP;
END LOOP;
IF revers THEN
FOR i IN REVERSE array_upper(exported,1) .. array_lower(exported,1) LOOP
RETURN NEXT exported[i];
END LOOP;
ELSE
FOR i IN array_lower(exported,1) .. array_upper(exported,1) LOOP
RETURN NEXT exported[i];
END LOOP;
END IF;
RETURN;
END;
' LANGUAGE plpgsql;
EOF

if [ ! -d postgresql ]; then
mkdir postgresql
else
rm postgresql/*
fi;
if [ ! -d postgresql ]; then
mkdir postgresql
else
rm postgresql/*
fi;

./intrain.sh

DATADIR=./home/okbob/`date +%Y%m%d`
echo "BEGIN;\n" >> postgresql/import.sql
for table in `psql -At -c "SELECT * FROM
list_user_tables_sort_depend('root','f');" intra`; do
TABLE=`echo $table|tr [[:lower:]] [[:upper:]]`
echo $TABLE
if [ -e $DATADIR/$TABLE.dat ]; then
cat $DATADIR/$TABLE.dat |./reformat.pl > postgresql/$TABLE.data
echo "\copy $table from '$TABLE.data' delimiter ',' null 'NULL'"
>> postgresql/import.sql
fi
done
echo "COMMIT;" >> postgresql/import.sql

echo "BEGIN;" >> postgresql/delete.sql
for table in `psql -At -c "SELECT * FROM
list_user_tables_sort_depend('root','t');" intra`; do
echo "delete from $table;" >> postgresql/delete.sql
done
echo "COMMIT;" >> postgresql/delete.sql
cat $DATADIR/dict.sql | ./get_seq.pl >> postgresql/get_seq.sql
rm -rf ./home

Regards
Pavel Stehule

On 29/12/2007, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> Is there an existing tool that can infer the functional dependencies
> implied by the keys in an existing database? Or just compute the
> canonical cover of a set of functional dependencies?
>
> Regards,
> Jeff Davis
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>