Обсуждение: reading table metadata from pl/pgsql in 8.2 under FreeBSD
Hi -
My apologies in advance if this is in the archives/documentation. I may not have used the correct search terms in trying to find the answer.
I've looked at the system tables, particularly pg_tables, pg_class, and pg_attribute. In oder to access the attributes for a particular table using the pg_attribute table, I need the oid for the table, specifically the pg_class.oid.
Looking at some tables, I believe that relfilenode is the the OID of the table. The corresponding field (i.e., foreign key) in pg_attribute is attrelid. So far so good. My problem is that in the pg_class table I don't know how to determine which entries are tables. And in the pg_attribute table I don't know how to determine which of the corresponding rows are attributes. (My current rule of thumb to identify an attribute is that in pg_attribute, the defined columns have attnum > 0; all others have attnum <0. )
Thanks for any advice/suggestions.
david
My apologies in advance if this is in the archives/documentation. I may not have used the correct search terms in trying to find the answer.
I've looked at the system tables, particularly pg_tables, pg_class, and pg_attribute. In oder to access the attributes for a particular table using the pg_attribute table, I need the oid for the table, specifically the pg_class.oid.
Looking at some tables, I believe that relfilenode is the the OID of the table. The corresponding field (i.e., foreign key) in pg_attribute is attrelid. So far so good. My problem is that in the pg_class table I don't know how to determine which entries are tables. And in the pg_attribute table I don't know how to determine which of the corresponding rows are attributes. (My current rule of thumb to identify an attribute is that in pg_attribute, the defined columns have attnum > 0; all others have attnum <0. )
Thanks for any advice/suggestions.
david
"David Monarchi" <david.e.monarchi@gmail.com> writes: > Looking at some tables, I believe that relfilenode is the the OID of the > table. No, OID is the OID of the table. The clue I think you are missing is that OID is a "system" column, which basically means that "SELECT *" doesn't show it. But if you do "SELECT oid,other-fields FROM pg_class" you'll see it. relfilenode is coincidentally equal to OID in the initial state of a pg_class entry, but it doesn't necessarily stay that way. If you use one where you should've used the other, your code will break eventually. > My problem is that in the pg_class table I don't > know how to determine which entries are tables. See relkind. > And in the pg_attribute > table I don't know how to determine which of the corresponding rows are > attributes. All of them. Depending on what you are doing, you might wish to ignore system columns (attnum < 0) and/or dropped columns (attisdropped = true). regards, tom lane