Thread: query for all values linked to a field
hi, i'm trying to output all values that can be reached from a specific record by a foreign key link. for example, if tblimage has image_id, image_info and tblstack has stack_id=tblimage.image_id, stack_info_1, stack_info_2 i want to be able to get image_id, image_info, stack_info_1, stack_info_2. of course, my database has many more tables and links. i can use information_schema to get the links and recursively troll through all the tables, i was just wondering if there's a way to do this with one query instead of programmatically. thanks, jenn -- View this message in context: http://postgresql.1045698.n5.nabble.com/query-for-all-values-linked-to-a-field-tp5072691p5072691.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, Dec 13, 2011 at 2:37 PM, anisoptera <dragonfly@elseworld.com> wrote: > hi, > > i'm trying to output all values that can be reached from a specific record > by a foreign key link. > > for example, if tblimage has image_id, image_info and tblstack has > stack_id=tblimage.image_id, stack_info_1, stack_info_2 > i want to be able to get image_id, image_info, stack_info_1, stack_info_2. > > of course, my database has many more tables and links. i can use > information_schema to get the links and recursively troll through all the > tables, i was just wondering if there's a way to do this with one query > instead of programmatically. I didn't quite parse that. Using recursive queries, it should be able to pretty easily query out, given a table name, all tables that link to that table and the columns of the keys in the constraints (and, if necessary, the links from other tables to those tables). If I was looking for specific *records*, not tables and columns, I would probably be generating a query from the above. (If I'm understanding you properly that is). merlin
Merlin Moncure-2 wrote > > > I didn't quite parse that. Using recursive queries, it should be > able to pretty easily query out, given a table name, all tables that > link to that table and the columns of the keys in the constraints > (and, if necessary, the links from other tables to those tables). > > If I was looking for specific *records*, not tables and columns, I > would probably be generating a query from the above. (If I'm > understanding you properly that is). > i'm trying to get all the values linked to a specific record, ideally with table/column names. so my output (given the structure i showed above) would be more like: tblimage image_id image_id-value tblimage image_info image_info-value tblstack stack_info_1 stack_info-value tblstack stack_info_2 stack_info-value i didn't know about recursive queries; guess i'll start by looking that up. thanks, jenn i didn't know about recursive queries; i'll look that up. -- View this message in context: http://postgresql.1045698.n5.nabble.com/query-for-all-values-linked-to-a-field-tp5072691p5072765.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.