How to automatically find the correct schema to store data inside a PostgreSQL database?

I have a C# WinForms application that imports sales contained in a fixed width file and stores it into some tables. The tables have the exact same structure but are located in different schemas inside the database.

Currently I run two queries in my application to store each sale's data, one to fetch the correct schema to store the data and another to actually insert it into the table.

To better exemplify, let's say I have a Clients table in a Main schema and several schemas (one for each region of my country) with a Sales table each. I use each sale's Client Id to find the correct schema to store it.

I need to optimize this proccess, make it faster. I profiled the application and this two queries are the most time consuming steps. I think running a single command inside my application and leaving the work of finding the correct schema to the database will help.

I want to achieve something like this:

INSERT INTO 
    (SELECT clients.schema_name FROM main.clients WHERE clie开发者_StackOverflow社区nt_id = @client_id).sales 
    (client_id, qty, value) 
VALUES 
    (@client_id, @qty, @value)

Clearly it doesn't works. I don't know much about SQL, just the basics, but I think that's not possible to do with in a single command, I guess I need a Stored Procedure for that.

I would love to hear some directions or even better solutions for this (as long it does not involves changing the database structure).

Your database is broken, and avoiding the problem is quite probably not going to be worth it long term. If you are interested, it's not difficult to point out the maintainability problems with using schemas like that.

That said, you probably cache client_id -> schema_name in your C# code and avoid most queries to get that information.

You could also write a SP to do that, but it would probably have to resort to constructing dynamic SQL queries or a similar hack which might be pretty slow by itself.