Re: Partitioned Database and Choosing Subtables

> -----Original Message-----
> From: Bill Thoen [mailto:bthoen(at)gisnet(dot)com]
> Sent: Monday, March 14, 2011 11:31 PM
> To: pgsql-general(at)postgresql(dot)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