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@j-davis.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
>