Обсуждение: Using wildcard for table name ?

--As of July 26, 2013 11:21:44 AM -0700, Francisco Leovey is alleged to
have said:

> I have a database in Postgres 9 with 75 tables, all have the exact same
> definition except for the name of the table.
> Each one contains data for people who are doctors (table name = doctors),
> nurses (table name = nurses) etc. etc.
> How can I do a select to find a given person's name (a single field)
> without mentioniing all 75 table names ? I do not need to know fron which
> table it came, just need to know if it exists.
>
> Is there a "select * from * where *.name ='Joe Blow';" kind of syntax ?
>
> I could join all tables into one to do the search but we want to keep
> data separated due to volume.
> Thanks

--As for the rest, it is mine.

This sounds like a database design problem.  May I suggest looking at using
one partitioned table instead?

<http://www.postgresql.org/docs/9.2/interactive/ddl-partitioning.html>

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Francisco Leovey wrote
> I could join all tables into one to do the search but we want to keep data
> separated due to volume.

Pre-mature optimization is bad.

Partitioning is one solution while it has problems they are considerably
less-worse than what you are stuck with currently.

That said the answer to your specific question is NO.  Since you only
provided enough information to answer your specific question any advice that
may be offered is suspect since we know very little about your actual
situation.

I am curious how you deal with duplicate names and what you want returned in
that situation.  It may be worth having a master "name directory" table with
two columns.  The first column would be the person name and the second
column would be an array of table names in which that person name appears.
You would need triggers (or CRUD functions) to maintain consistency.  You
could then write a function that queries this table to find out which other
tables the name appears on and then you can create dynamic SQL queries for
just those tables.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Using-wildcard-for-table-name-tp5765314p5765317.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On Sat, Jul 27, 2013 at 12:26 AM, Francisco Leovey <fleovey@yahoo.com> wrote:
> My question was solved by writing a Perl program that gets the table names
> as a list from the database structure and then simply searching each table.
> Years ago I worked with Informix and there was a syntax for using a variable

Will I believe partitioning is a better idea, why don't you provide a
view that unions all the table you are interested in? So far it will
be a consisnte SQL interface and you will not need any external
application.

Luca