Create id string for grouped view

Question

I am trying to create a view with line numbers as follows:

create or replace view daily_transactions as select generate_series(1, count(t)) as id, t.ic, t.bio_id, t.wp, date_trunc('day', t.transaction_time)::date transaction_date, min(t.transaction_time)::time time_in, w.start_time wp_start, w.start_time - min(t.transaction_time)::time in_diff, max(t.transaction_time)::time time_out, w.end_time wp_end, max(t.transaction_time)::time - w.end_time out_diff, count(t) total_transactions, calc_att_status(date_trunc('day', t.transaction_time)::date, min(t.transaction_time)::time, max(t.transaction_time)::time, w.start_time, w.end_time ) status from transactions t left join wp w on (t.wp = w.wp_name) group by ic, bio_id, t.wp, date_trunc('day', transaction_time), w.start_time, w.end_time; 

I ended up with duplicate lines. SELECT DISTINCT does not work either. Any ideas?

Transaction Table:

 create table transactions( id serial primary key, ic text references users(ic), wp text references wp(wp_name), serial_no integer, bio_id integer, node integer, finger integer, transaction_time timestamp, transaction_type text, transaction_status text ); 

WP table:

 create table wp( id serial unique, wp_name text primary key, start_time time, end_time time, description text, status text ); 

View Result:

Answer 1

 CREATE OR REPLACE VIEW daily_transactions as SELECT row_number() OVER () AS id , t.ic , t.bio_id , t.wp , t.transaction_time::date AS transaction_date , min(t.transaction_time)::time AS time_in , w.start_time AS wp_start , w.start_time - min(t.transaction_time)::time AS in_diff , max(t.transaction_time)::time AS time_out , w.end_time AS wp_end , max(t.transaction_time)::time - w.end_time AS out_diff , count(*) AS total_transactions , calc_att_status(t.transaction_time::date, min(t.transaction_time)::time , max(t.transaction_time)::time , w.start_time, w.end_time) AS status FROM transactions t LEFT JOIN wp w ON t.wp = w.wp_name GROUP BY t.ic, t.bio_id, t.wp, t.transaction_time::date , w.start_time, w.end_time; 

Highlights

• generate_series() is applied after aggregate functions, but creates several lines , thereby multiplying all output lines.
  window function row_number() also applied after aggregate functions, but generates only one number per line . For this you need PostgreSQL 8.4 or later.

• date_trunc() redundant in date_trunc('day', t.transaction_time)::date .
  t.transaction_time::date achieves the same, simpler and faster.

• Use count(*) instead of count(t) . Same result here, but a little faster.

Some other minor changes.