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

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

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

> 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.

Hi, and thanks for your input.

However, logic and clarity don't always go hand in hand with
legacy apps :-(

> 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:

Yes, as you so eloquently put it, that would be great, but it's a no
can do I'm afraid!

I was thinking about doing a PIVOT on the fieldlist in the table
and then constructing an SQL string using IN(name1, name2.....).
Does this make any sense?


Paul...

Hi, and thanks for your input.

>> 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.


> OK, we won't blame you.

Phew! :-)


> But since the tables are identical, have you
> considered inheritance?


Ah, therein lies the rub! This system has to work on MS SQL Server
and Oracle - I'll look into this solution there also.


> Create table allsalespersons... to have a master table with the same
> structure as the individual salesperson.

> For existing tables, convert them to child tables:
> alter table fred inherit allsalespersons;
> ...
> For new tables just create them as inherited to begin with.
> Now you can select from "allsalespersons" and get everyone.


It's certainly an elegant solution - I'll just have to see what can be done on
other RDBMSs - thanks again for your input.


Paul...


> Steve

Hi all,

I have an easy problem - just can't get my head around it.

I have a domain id and a date - what I want is the 3 highest
dates by domain id.

I prepared a complete schema (see below). The result I want is


1, 2016-02-24
1, 2016-02-25
1, 2016-02-26
2, 2016-02-25
2, 2016-02-26
2, 2016-02-27
3, 2016-03-27
3, 2016-03-28
3, 2016-03-29


I've tried a couple of things, but my brain isn't working tonight :-)

select max(distinct(domain_id)), max(d_date)
from dom_test
group by domain_id, d_date
limit 3;

select domain_id, max(d_date) from
(
 select distinct(domain_id), d_date from dom_test
 group by domain_id
) tab;


My schema.

create table dom_test(domain_id int, d_date date);

insert into dom_test values(1, '2016-02-23');
insert into dom_test values(1, '2016-02-24');
insert into dom_test values(1, '2016-02-25');
insert into dom_test values(1, '2016-02-26');
insert into dom_test values(2, '2016-02-23');
insert into dom_test values(2, '2016-02-24');
insert into dom_test values(2, '2016-02-25');
insert into dom_test values(2, '2016-02-26');
insert into dom_test values(2, '2016-02-27');
insert into dom_test values(3, '2016-02-23');
insert into dom_test values(3, '2016-02-24');
insert into dom_test values(3, '2016-02-25');
insert into dom_test values(3, '2016-02-26');
insert into dom_test values(3, '2016-03-27');
insert into dom_test values(3, '2016-03-28');
insert into dom_test values(3, '2016-03-29');

Hi Paul,

If I'm understanding what you're trying to do, I think a window function
will be your friend:

http://www.postgresql.org/docs/9.3/static/tutorial-window.html

Something like this should do the trick:

SELECT p.domain_id,p.d_date FROM (SELECT
      domain_id,
      d_date,
      rank() OVER (PARTITION BY domain_id ORDER BY d_date DESC) AS pos
   FROM dom_test) AS p
WHERE
   p.pos < 3;

On Tue, Mar 22, 2016 at 09:51:57PM +0000, Paul Linehan wrote:
> Hi all,
>
> I have an easy problem - just can't get my head around it.
>
> I have a domain id and a date - what I want is the 3 highest
> dates by domain id.
>
> I prepared a complete schema (see below). The result I want is
>
>
> 1, 2016-02-24
> 1, 2016-02-25
> 1, 2016-02-26
> 2, 2016-02-25
> 2, 2016-02-26
> 2, 2016-02-27
> 3, 2016-03-27
> 3, 2016-03-28
> 3, 2016-03-29
>
>
> I've tried a couple of things, but my brain isn't working tonight :-)
>
> select max(distinct(domain_id)), max(d_date)
> from dom_test
> group by domain_id, d_date
> limit 3;
>
> select domain_id, max(d_date) from
> (
>  select distinct(domain_id), d_date from dom_test
>  group by domain_id
> ) tab;
>
>
> My schema.
>
> create table dom_test(domain_id int, d_date date);
>
> insert into dom_test values(1, '2016-02-23');
> insert into dom_test values(1, '2016-02-24');
> insert into dom_test values(1, '2016-02-25');
> insert into dom_test values(1, '2016-02-26');
> insert into dom_test values(2, '2016-02-23');
> insert into dom_test values(2, '2016-02-24');
> insert into dom_test values(2, '2016-02-25');
> insert into dom_test values(2, '2016-02-26');
> insert into dom_test values(2, '2016-02-27');
> insert into dom_test values(3, '2016-02-23');
> insert into dom_test values(3, '2016-02-24');
> insert into dom_test values(3, '2016-02-25');
> insert into dom_test values(3, '2016-02-26');
> insert into dom_test values(3, '2016-03-27');
> insert into dom_test values(3, '2016-03-28');
> insert into dom_test values(3, '2016-03-29');
>
>
> --
> 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

Oops, obviously that should be "p.pos <= 3", but you get the idea...

On Tue, Mar 22, 2016 at 02:59:41PM -0700, Skylar Thompson wrote:
> Hi Paul,
>
> If I'm understanding what you're trying to do, I think a window function
> will be your friend:
>
> http://www.postgresql.org/docs/9.3/static/tutorial-window.html
>
> Something like this should do the trick:
>
> SELECT p.domain_id,p.d_date FROM (SELECT
>       domain_id,
>       d_date,
>       rank() OVER (PARTITION BY domain_id ORDER BY d_date DESC) AS pos
>    FROM dom_test) AS p
> WHERE
>    p.pos < 3;
>
> On Tue, Mar 22, 2016 at 09:51:57PM +0000, Paul Linehan wrote:
> > Hi all,
> >
> > I have an easy problem - just can't get my head around it.
> >
> > I have a domain id and a date - what I want is the 3 highest
> > dates by domain id.
> >
> > I prepared a complete schema (see below). The result I want is
> >
> >
> > 1, 2016-02-24
> > 1, 2016-02-25
> > 1, 2016-02-26
> > 2, 2016-02-25
> > 2, 2016-02-26
> > 2, 2016-02-27
> > 3, 2016-03-27
> > 3, 2016-03-28
> > 3, 2016-03-29
> >
> >
> > I've tried a couple of things, but my brain isn't working tonight :-)
> >
> > select max(distinct(domain_id)), max(d_date)
> > from dom_test
> > group by domain_id, d_date
> > limit 3;
> >
> > select domain_id, max(d_date) from
> > (
> >  select distinct(domain_id), d_date from dom_test
> >  group by domain_id
> > ) tab;
> >
> >
> > My schema.
> >
> > create table dom_test(domain_id int, d_date date);
> >
> > insert into dom_test values(1, '2016-02-23');
> > insert into dom_test values(1, '2016-02-24');
> > insert into dom_test values(1, '2016-02-25');
> > insert into dom_test values(1, '2016-02-26');
> > insert into dom_test values(2, '2016-02-23');
> > insert into dom_test values(2, '2016-02-24');
> > insert into dom_test values(2, '2016-02-25');
> > insert into dom_test values(2, '2016-02-26');
> > insert into dom_test values(2, '2016-02-27');
> > insert into dom_test values(3, '2016-02-23');
> > insert into dom_test values(3, '2016-02-24');
> > insert into dom_test values(3, '2016-02-25');
> > insert into dom_test values(3, '2016-02-26');
> > insert into dom_test values(3, '2016-03-27');
> > insert into dom_test values(3, '2016-03-28');
> > insert into dom_test values(3, '2016-03-29');
> >
> >
> > --
> > 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
>
>
> --
> 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