Re: Tricky SQL problem - retrieve information_schema info and make use of it.

Hi Paul,

I wonder if a simpler solution would be to fix the table layout; make that
single salesperson table that you agree would have been better. Then, make
a view on top of that for each salesperson. As of PostgreSQL 9.3, views are
updatable---that is, they map INSERT/UPDATE/DELETE to the underlying
table---as long as the source data of the column is unambiguous:

http://www.postgresql.org/docs/current/static/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS

This would let code that depends on the "first name" table use the views,
but you could query the underlying table that has all the salespeople in
one spot.

On Mon, Mar 21, 2016 at 10:48:30PM +0000, Paul Linehan wrote:
> Hi all,
>
>
> I'll explain the problem and then I'll give you a schema you should
> be able to cut and paste.
>
> I  have a system which has different tables for each salesman (please
> don't blame me for this snafu). **Same** table structure for each
> person.
>
> I want to be able to query results for each salesman - but new sales
> personnel are being added and deleted all the time, so a static list
> is not appropriate.
>
> So, in my example, I have two tables, one for geoffrey and one for
> jorge - I want to be able to pick up results from both of their tables
> (and the list is obviously longer than 2 - but, as I say, **may** vary
> day by day - with a different table when somebody is added or deleted.
> An SQL solution is necessary.
>
> As an example, here is a query which returns data for geoffrey.
>
> Select a.firstname, a.lastname, a.address, a.city,
> a.state, a.zip, a.phone,   a.itemsold, a.saledate,
> b.dbname, b.managernotes, b.regionalmanager,
> b.districtmanager, b.incentiveitem
> FROM geoffrey a
> INNER JOIN managerrevieweddata b
> ON a.recordID = b.recordID
> and a.saledate = '2016-01-21';
>
> What I want is to return data for every sales person by getting that
> information out of the system tables.
>
>
> Now, to show that I haven't been completely lazy, I wrote a query
> which returns a list of those who have a table with a field which is
> their own table name (geoffrey and jorge). This query depends on
> their being a "firstname" field - perhaps not the best? For starters,
> we can work on the assumption that no other table has this (bonus,
> base the query on **all** the fields)
>
> Here are my attempts to start tackling this problem - Oh,
> did I mention, it has to be SQL.
>
> First, to get the two current sales persons
>
>   SELECT table_name, ordinal_position
>   FROM information_schema.columns
>   WHERE table_schema = 'public'
>   AND (column_name = 'firstname') ;
>
> and then to get the columns in those tables (.* will get more data, but
> I haven't been able to figure out how to turn it into a query)
>
> select table_name, column_name, ordinal_position
> from information_schema.columns --where table_schema = 'public';
> where table_name in
> (
>    SELECT DISTINCT(table_name)
>   FROM information_schema.columns
>   WHERE table_schema = 'public'
>   AND (column_name = 'firstname')
> )
> -- and ordinal_position <> 1
> order by table_name, ordinal_position
>
>
> Finally, an entire schema which you can copy and paste.
>
> Create Table ReviewDB
> (
>   TableName varchar(500)
>   ,ServerDBTable varchar(1000)
> );
>
> Insert Into ReviewDB VALUES
> ('geoffrey', 'beans.franks.dbo.geoffrey')
> ,('jorge', 'smallpox.virus.dbo.jorge')
> ,('mitch', 'mosquito.insect.dbo.mitch');
>
> Create Table geoffrey
> (
>   recordID SERIAL PRIMARY KEY
>   ,firstname varchar(500)
>   ,lastname varchar(500)
>   ,address varchar(500)
>   ,city varchar(500)
>   ,state varchar(500)
>   ,zip varchar(500)
>   ,phone varchar(500)
>   ,itemsold varchar(500)
>   ,saledate date
>   ,managerapproved varchar(500)
> );
>
> Insert Into geoffrey VALUES
> (1,'manny', 'ramirez', '1111111 aoaswdfrljkasdf ', 'topaz', 'mn',
> '1111', '9995552222', 'sofa', '01/21/2016', '')
> ,(2,'hi', 'ho', '2323 aoaswdfrljkasdf ', 'topaz', 'mn', '1111',
> '6662229888', 'chair', '02/21/2016', '')
> ,(3,'ee', 'aa', '4646 aoaswdfrljkasdf ', 'topaz', 'mn', '1111',
> '3332221919', 'ottoman', '01/21/2016', '');
>
>
>
> Create Table jorge
> (
>   recordID serial PRIMARY KEY
>   ,firstname varchar(500)
>   ,lastname varchar(500)
>   ,address varchar(500)
>   ,city varchar(500)
>   ,state varchar(500)
>   ,zip varchar(500)
>   ,phone varchar(500)
>   ,itemsold varchar(500)
>   ,saledate date
>   ,managerapproved varchar(500)
> );
>
> Insert Into jorge VALUES
> (1,'aa', 'bb', '1111111 c street ', 'holt', 'tn', '2222',
> '1113334444', 'sofa', '01/21/2016', '')
> ,(2,'cc', 'ddo', '2323 b ', 'holt', 'tn', '2222', '8889997788',
> 'chair', '02/21/2016', '')
> ,(3,'mm', 'rr', '4646 e street ', 'holt', 'tn', '2222', '8889998877',
> 'ottoman', '03/21/2016', '');
>
> Create Table managerrevieweddata
> (
>   ID serial PRIMARY KEY
>   ,recordID int
>   ,dbname varchar(500)
>   ,managernotes text
>   ,regionalmanager varchar(500)
>   ,districtmanager varchar(500)
>   ,incentiveitem varchar(500)
> );
>
>
> Insert Into managerrevieweddata VALUES
>  (1, 1, 'geoffrey', 'Valid sale, remind to offer upsell next time.',
> 'Regional Manager', 'District Manager', 'No')
> ,(2, 2, 'jorge', 'Review with salesman', 'Regional Manager', 'District
> Manager', 'NO');
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

--
-- Skylar Thompson (skylar2@u.washington.edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine