Re: [GENERAL] functional dependency tool

From: Pavel Stehule <pavel.stehule(at)gmail.com>
Date: Sun Dec 30 2007 - 03:52:41 EST

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
>

---------------------------(end of broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster Received on Sun Dec 30 03:54:02 2007