in reply to Re^2: Hashing temporal tables in PostgreSQL
in thread Hash generate and find tampered tables in PostgreSQL

If you want the schema information to hash it, the most generic way would be the catalog methods of DBI:

my $sth = $dbh->table_info();
my $tables = $sth->fetchall_arrayref();
print Dumper @$tables;

[download]

Replies are listed 'Best First'.
Re^4: Hashing temporal tables in PostgreSQL
by gtk (Acolyte) on Sep 25, 2017 at 04:13 UTC
    Hi Corion, Hashing here means not taking values to a hash, what I meant was to generate a hash/checksum(by using hashing algo like sha1,sha256,md5, etc) value in each table definition in each schema. As an example below is the test file i am generating in Oracle database. In there, I have created temporary table and generate hash values and inserted into a temporary table. #SCHEMA_NAME,OBJECT_TYPE,TABLE_NAME,HASH_VALUE,CREATED_TIME TEST_USER,TABLE,USER_DETAILS,30D841C3EEA693D1436D9B7978903527F9D0DDB6,25-SEP-17
[reply]

      So that hashvalue

      30D841C3EEA693D1436D9B7978903527F9D0DDB6

      is the result of hashing the string "#SCHEMA_NAME,OBJECT_TYPE,TABLE_NAME,HASH_VALUE,CREATED_TIME TEST_USER,TABLE,USER_DETAILS" ?

      Isn't that trivially done in postgres easily with Corion's retrieval values (or mine), and postgres' md5() function (or similar functions from perl, or pgcrypto) ? Where are you having problems?

      Also, do you mean 'temporary' where you wrote 'temporal'? And why 'temporary', or 'temporal'?

[reply]
[d/l]
        No, Erix, Sorry for the miscommunication. What I meant was that the CSV file which read all tables and generate a hash/checksum value for each table. And it is not temporary or temporal. Its about identify "tampered" tables by using the pre-generated hash/checksum values.
[reply]

In Section Seekers of Perl Wisdom