Have you considered using a stored proceedure? It seems like it might easily
give you the logic you're after.
http://www.postgresql.org/docs/8.1/static/xplang.html
On Sun, 12 Mar 2006, sconeek@gmail.com wrote:
> hi all,
>
> i have a web based java application with a postgres db.
>
> now i am trying to generate a temp table which contains all hour
> records for a selected date range. eg. if the user selects 2006-03-14
> as from and 2006-03-14 as to, the system should insert 24 hour records
> with their individual time stamps (eg 2006-03-14 12:00:00).
>
> now currently, i have a select statement which searches another table,
> finds data and then inserts them. but the problem is that if there is
> no data for an hour, it does not insert anything.
>
> now i can think of a couple of solutions,
> 1. insert additional records using a second statement, which compares
> the table and inserts any missing records
> 2. insert data as usual and then using a second statement, compare the
> content and insert any missing records.
>
> my current implementation is:
> " INSERT INTO temp_table (edit_time,edit_time_count) " +
> " SELECT to_timestamp(to_char (last_edit_timestamp,'YYYY-MM-DD
> HH24:00:00'),
> 'YYYY-MM-DD HH24:00:00')AS edit_time, " +
> " count(to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00')) as
> edit_time_count " +
> " FROM " + tableName + " " + sqlWhereStr +
> " GROUP BY to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00') " +
> " ORDER BY to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00'); ";
>
> the problem is that if the select statement does not return anything
> for a particular hour record, that record is not inserted. now i would
> like to have a table with all records including hours with 0 count.
>
> can somebody please help me urgently. i would really appreciate it.
> thanks.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>