> -----Original Message----- > From: Bill Thoen [mailto:bth...@gisnet.com] > Sent: Monday, March 14, 2011 11:31 PM > To: pgsql-general@postgresql.org > Subject: Partitioned Database and Choosing Subtables > > I've got a ver 8.4.5 partitioned data base with records > organized by US state, so the partitions are set up by state. > When I query this database and include the key field that > tells postgres what partition you , everything works as I > expect. It searches only the specified partition, and it's > fast . But that's only if I use a constant, like this: > > SELECT lions, tigers, bears FROM WildLife WHERE state_pt = 'CO'; > > What I want to be able to do is put this key value in a table > and PG look in whatever partition the column specifies, like so: > > SELECT lions, tigers, bears, statecode FROM WildLife WHERE > state_pt = statecode; > > However when I try anything other than a constant, it search > EVERY partition, sequentially, which is not what I want it to > do. So is there any way to specify the partition to search > using a variable/column name? > > -- > *Bill Thoen* > GISnet - www.gisnet.com > 303-786-9961 >
Try using "dynamic" sql: EXECUTE 'SELECT lions, tigers, bears, statecode FROM WildLife WHERE state_pt = ' || statecode INTO ...; See: http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
