Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2001
    Posts
    6

    Unanswered: Obtaining table definition

    Hello folks,

    I'm new to postgresql and I need some help. Here's the situation: in the database I inherited, there's a table called all_tables that contains information about all the other tables in the database, such as table_name, column_name, column_length. This table was populated manually as the tables were created, for example, you created table xyz then you would add an entry into all_tables that would be something like table_name(xyz), field1(varchar20), field2(varchar20) and so on. Those entries were later used for forms, etc. Now, what happened is that many times people made mistakes and what's in all_tables doesn't necessarily match the real tables. In the example above, the entry would say, table_name(xyz), field1(varchar35), field2(varchar50) instead of the real column definitions. If this was Oracle, I wouldn't need a table like this one, that contains information about the rest of the tables; Oracle would take care of this automatically and a query to dba_tab_columns would always return the real information. Is there anything similar in postgres, or is there any way to retrieve the column information from the tables, and insert/update my "all_columns" table automatically? Something like, "insert into all_tables (select table_name, column_name, column_size from pg_whatever)"
    Now I'm doing it manually, table by table, column by column, and it's killing me. Let alone the mistakes that I may make to compound the situation.

    Any help will be appreciated.

    Thanks!
    Gabriel.

  2. #2
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    2

    Re: Obtaining table definition

    Originally posted by hp9000
    Hello folks,

    I'm new to postgresql and I need some help. Here's the situation: in the database I inherited, there's a table called ...

    Any help will be appreciated.

    Thanks!
    Gabriel.
    do you know pg_dump?

  3. #3
    Join Date
    Nov 2001
    Posts
    6
    For all I know, pg_dump is used to make backups and restores. I'm not sure how it could help me obtaining the meta-data I need, but any suggestions will be welcome.

    Gabriel.

  4. #4
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    2
    try this:

    pg_dump -C -D -f your_export_sql_file_name your_db_name

  5. #5
    Join Date
    Nov 2003
    Location
    SW London
    Posts
    5
    Well, there's the pg_tables table that lists the tables in the database (all on this server?) - select all the ones with a schemaname of "public" and maybe take a look at the pg_database table (or whatever it's called) to see if that'll help make sure you only pick tables for your database???

    I want to see what the fields are to help me out with a trigger; I've written a function that will get called as a trigger from lots of tables, and I need to know whether a particular field exists in the table it's called with in any one trigger firing... It's a common field, and I know I'm going to have to dynamically construct a query for this but was wondering if it might be simpler to create a function for every table named after the table (easy to construct the function name from the table name then) and call it to get the data I need.

    Can I write the functions per table ok? that all take in the NEW/OLD record (as appropriate) from the trigger function as a 'RECORD' argument type, and return type is 'TEXT'??

    I'll give it a go... But if you can respond or suggest a better way of doing it (this is for an automatic audit log of edits on the database).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •