0

I have a big table on postgresql and I want to get multiple result sets as an output of a SELECT query if is it possible ?

My table looks like this: 

select * from dns_table

id  |    dns_name    | ip_source  |    mac_source     |         query_time         
-----+----------------+------------+-------------------+----------------------------
   1 | google.com     | /10.0.0.1  | 00-00-00-00-00-01 | 2014-05-12 10:03:03.503938
   2 | facebook.com   | /10.0.0.1  | 00-00-00-00-00-01 | 2014-05-12 10:03:04.567417
   3 | google.com     | /10.0.0.5  | 00-00-00-00-00-05 | 2014-05-12 10:03:05.372453
   4 | youtube.com    | /10.0.0.1  | 00-00-00-00-00-01 | 2014-05-12 10:03:05.636633
   5 | facebook.com   | /10.0.0.5  | 00-00-00-00-00-05 | 2014-05-12 10:03:06.420067
   6 | yahoo.com      | /10.0.0.1  | 00-00-00-00-00-01 | 2014-05-12 10:03:06.685871
   7 | google.com     | /10.0.0.6  | 00-00-00-00-00-06 | 2014-05-12 10:03:07.198905
   8 | youtube.com    | /10.0.0.5  | 00-00-00-00-00-05 | 2014-05-12 10:03:07.524503
   9 | wikipedia.org  | /10.0.0.1  | 00-00-00-00-00-01 | 2014-05-12 10:03:07.747143
  10 | facebook.com   | /10.0.0.6  | 00-00-00-00-00-06 | 2014-05-12 10:03:08.287584
  11 | yahoo.com      | /10.0.0.5  | 00-00-00-00-00-05 | 2014-05-12 10:03:08.572827
  ...
  ...

What I want is : 

SELECT 
    count(dns_name) as hits, 
    dns_name, 
    to_char(query_time - interval '1 month','YYYY,MM,DD,HH24,MI') as qt 
FROM dns_table 
where dns_name like '**google.com**' 
group by dns_name, qt 
order by qt;

hits |  dns_name  |        qt        
------+------------+------------------
  61 | google.com | 2014,04,12,10,03
  40 | google.com | 2014,04,12,15,05
  45 | google.com | 2014,04,13,09,34

So I'm wondering if is it possible to replace google.com with a parameter in order to obtain the result set for each dns_name (google.com, facebook.com, yahoo.com, ...).

Don't hesitate for any questions !

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
midobAck
  • 15
  • 5
  • I don't really understand... you do not get a "table" as a result of a select query and I don't see how it would be possible to get "sub tables". How would you expect it to behave? You can run separate queries for each parameter. – mareckmareck May 13 '14 at 13:16
  • 2
    Are you just looking to remove the `where` criteria and add `dnsname` to the group by? – sgeddes May 13 '14 at 13:18
  • what I want it's to not repeating same query for all dns_name in the table (too tedious) ! – midobAck May 13 '14 at 13:20
  • some thing like that : SELECT count(dns_name) as hits, dns_name, to_char(query_time - interval '1 month','YYYY,MM,DD,HH24,MI') as qt FROM dns_table where dns_name like 'google.com' group by dns_name, qt order by qt; --- SELECT count(dns_name) as hits, dns_name, to_char(query_time - interval '1 month','YYYY,MM,DD,HH24,MI') as qt FROM dns_table where dns_name like 'yahoo.com' group by dns_name, qt order by qt; ---- – midobAck May 13 '14 at 13:21
  • Alternatively, if you want to supply a list of names, can you not use `IN` (i.e. `where dns_name in ('google.com','yahoo.com')`). – sgeddes May 13 '14 at 13:25
  • @sgeddes because I want to use it in HighStock (each table is a series) – midobAck May 13 '14 at 13:27
  • You can't get multiple *result sets* from a single query, if that's what you ask (a table is far more than row sets). – pozs May 13 '14 at 13:31
  • It's not clear what you want. If you remove the where clause, you don't only get Google, but also facebook etc. As mentioned you can also replace your where clause with IN. What else do you want? Please show desired output. – Thorsten Kettner May 13 '14 at 13:50

1 Answers1

0

You can't get multiple result sets. You will have to ask for each individual series from your application. Fisrt get all the names:

select distinct dns_name
from dns_table
order by dns_name;

Then ask for the series passing the name as parameter:

select 
    count(dns_name) as hits, 
    dns_name, 
    date_trunc('minute', query_time) as qt 
from dns_table
where
    query_time > now() - interval '1 month'
    and
    dns_name = :dns_name
group by qt 
order by qt;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260