Tricky SQL problem - retrieve information_schema info and make use of it. - Mailing list pgsql-novice

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');