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.
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).