Re: [SQL] Cast on character columns in views
Luiz K. Matsumura wrote: Richard Broersma Jr wrote: --- "Luiz K. Matsumura" <[EMAIL PROTECTED]> wrote: CREATE VIEW view1( id, col1, type1, type2) AS SELECT table1.id, table1.col1, CAST( table2.type1 AS CHARACTER( 3 )), NULL FROM table1 JOIN table2 ON table2.fk_table1 = table1.id UNION ALL SELECT table1.id, table1.col1, CAST( NULL AS CHARACTER( 3 )), table3.type2 FROM table1 JOIN table3 ON table3.fk_table1 = table1.id; Would the above changes work? Regards, Richard Broersma Jr. Hi Richard, Your changes works ! But now I know what mistake I did: The error is occurring because I'm doing a CREATE OR REPLACE VIEW command. The command with null:character(3) works too. The error is because I create a view then try to change the definition with the CREATE OR REPLACE VIEW command When I drop the view first , and then create again the view (in a separated transaction), now the command works! (this is a bug?) I'm using a postgres 8.2.4 on Linux. Thanks a lot! Ugh, I'm totally crazy with this views I'm using pgadmin with postgres, when I clink on "view the data of selected object" button all works fine. But when I open a query tool window and do: SELECT * FROM view1; Now, again type1 column returns as bpchar. But if I do: SELECT type1 FROM view1; Now, type1 column returns as character(3) If I do select id, col1, type1,type2 from view1; Again type1 returns as bpchar. But if I do select id, col1,type2, type1 from view1; Now type1 return as character(3). -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Partial index on boolean - Sometimes fails to index scan
This is a reformulation of an earlier question. I've got a confusing
case of a partial index not working. The column in question is a
not-null boolean, which is false only for the most recent entries into
the table.
# explain analyze select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_
from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=2
)and(vehicleeve0_.RECONCILED=false);
QUERY PLAN
--
Aggregate (cost=49184.62..49184.64 rows=1 width=4) (actual
time=2017.793..2017.794 rows=1 loops=1)
-> Seq Scan on eg_vehicle_event vehicleeve0_
(cost=0.00..49162.93 rows=8679 width=4) (actual time=1202.175..2006.169
rows=10342 loops=1)
Filter: ((cso_id = 2) AND (NOT reconciled))
Total runtime: 2018.052 ms
stage=# create index eg_ve_reconciled_partial on eg_vehicle_event
(reconciled) where reconciled=false;
stage=# select pg_total_relation_size('eg_ve_reconciled_partial');
204800
# explain analyze select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_
from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=2
)and(vehicleeve0_.RECONCILED=false);
QUERY PLAN
---
Aggregate (cost=81.75..81.76 rows=1 width=4) (actual
time=56.218..56.219 rows=1 loops=1)
-> Index Scan using eg_ve_reconciled_partial on eg_vehicle_event
vehicleeve0_ (cost=0.00..60.05 rows=8679 width=4) (actual
time=0.118..44.647 rows=10342 loops=1)
Index Cond: (reconciled = false)
Filter: (cso_id = 2)
Total runtime: 56.312 ms
Which is all good. But the Hibernate version
of query still takes several seconds, and still appears in my pg_log
slow query log:
LOG: duration: 2248.662 ms statement: EXECUTE C_51443 [PREPARE:
select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from
EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=$1
)and(vehicleeve0_.RECONCILED=$2 )]
A full index on 'reconciled' speeds up the query. But why should the
partial index not also do it? Any idea why apparently identical
queries give different partial index scan results? PostgreSQL 8.1.9.
--
Visit http://www.obviously.com/
Re: [SQL] Difference between "foo is false" and "foo=false"? Partial index on boolean.
Bryce Nesbitt wrote: Tom Lane wrote: Bryce Nesbitt <[EMAIL PROTECTED]> writes: They give different results for NULL --- specifically, NULL for the former and FALSE for the latter. Don't blame me, it's in the spec... Thanks, and Got It. This particular column is: reconciled | boolean | not null On PostgreSQL 8.1.9. So given all that, why would the Hibernate query fail to use the partial index? I eventually created three indexes, and only the hideously large full index increases performance: Only the full index prevents a "false" scan from taking 4 seconds: LOG: duration: 4260.575 ms statement: EXECUTE C_50292 [PREPARE: select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=$1 ) and (vehicleeve0_.*RECONCILED=$2* )] It's a prepared query-plan, which means it can't plan to use the index because the next EXECUTE might have reconciled=true. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
Hello,
I have a large upddate to perform on tables which are dynamically
generated (dynamic names).
In this simplified example, the operation should replace in each family
the "mygroup" of each item of rang=0 with the "mygroup" value of the
element of rang=1 :
(the * indicate the modified values)
idfamily rang mygroup
1 10 1
2 11 2
3 12 3
4 13 4
5 20 6
6 21 6
7 22 7
8 23 7
9 30 10
10 31 20
11 32 21
After the update:
1 10 2 *
2 11 2
3 12 3
4 13 4
5 20 6
6 21 6
7 22 7
8 23 7
9 30 20 *
1031 20
1132 21
In the following function, I would like to use a prepared statement for
the update command but I get stuck with the tho different meanings of
EXECUTE ...
Is there a way to achieve this ?
Thanks,
Marc
CREATE OR REPLACE FUNCTION test_function(tablename varchar)
RETURNS integer AS
$BODY$
DECLARE
rec record;
top_group int;
top_family character(16);
top_id int;
BEGIN
/*
the prepared statement must be generated dynamically in order to
include the table name.
*/
EXECUTE 'PREPARE update_stmt (int, int) AS
update '||tablename||' set mygroup= $1 where id = $2';
/*
using "select distinct on" allows to retrieve and sort the required
information for the update.
this is faster than a self join on the table
*/
for rec in execute
'select DISTINCT on (family,rang)
family, rang, mygroup, id
from '||tablename||'
where rang < 2
order by family, rang'
loop
IF rec.rang = 0 THEN
top_group := rec.mygroup;
top_family := rec.family;
top_id := rec.id;
ELSIF rec.family = top_family AND rec.mygroup <> top_group THEN
/*
Update without using a prepared statement
EXECUTE 'update '||tablename||' set mygroup=
'||rec.mygroup||' where id = '||top_id;
*/
-- This works, but the command has to be computed for each
iteration
EXECUTE 'EXECUTE
update_stmt('||rec.mygroup||','||top_id||')';
/*
Following syntax would be fine
PERFORM EXECUTE update_stmt(rec.mygroup,top_id);
*/
END IF;
end loop;
DEALLOCATE update_stmt;
RETURN 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
/*
test data:
=== */
--drop table test_table;
create table test_table(id int,family int,rang int,mygroup int);
insert into test_table values (1,1,0,1);
insert into test_table values (2,1,1,2);
insert into test_table values (3,1,2,3);
insert into test_table values (4,1,3,4);
insert into test_table values (5,2,0,6);
insert into test_table values (6,2,1,6);
insert into test_table values (7,2,2,7);
insert into test_table values (8,2,3,7);
insert into test_table values (9, 3,0,10);
insert into test_table values (10,3,1,20);
insert into test_table values (11,3,2,21);
select test_function('test_table');
select * from test_table order by id;
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
Hello Marc, at first I tried to solve your update of the tables. The example you gave should be done with an update statement like the following: update test_table set mygroup=(select t.mygroup from test_table as t where t.family = test_table.family and t.rang = test_table.rang+1) where rang=0; If you have to write a function which receives the tablename as an argument it would look like: CREATE OR REPLACE FUNCTION test_function(tablename text) RETURNS integer AS $BODY$ BEGIN EXECUTE 'update ' || tablename || ' set mygroup=(select t.mygroup from ' || tablename || ' as t where t.family = test_table.family and t.rang = test_table.rang+1) where rang=0;' RETURN 0; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Generally you should avoid using explicit for/loop constructs in your stored procedures if the action can be solved by a single SQL statement, because the optimizer can make a better execution plan. -- ---> Dirk Jagdmann > http://cubic.org/~doj -> http://llg.cubic.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Use of delete...returning in function problem
I'm trying to use a delete statement with returning clause in a function:
CREATE OR REPLACE FUNCTION "public"."test_delete"() RETURNS void AS
$body$
DECLARE
rec billing_errors_new;
BEGIN
FOR rec IN (
delete from billing_errors_new where errortypeid IN (1,2) returning *)
LOOP
RAISE NOTICE 'billingid: % - errortypeid: %', rec.billingid,
rec.errortypeid;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I get following error though:
ERROR: syntax error at or near "delete" at character 4
QUERY: ( delete from billing_errors_new where errortypeid IN (1,2)
returning *)
CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5
According to the manual (I think) it should be possible:
The query used in this type of FOR statement can be any SQL command that
returns rows to the caller:
SELECT is the most common case, but you can also use INSERT, UPDATE, or
DELETE with a RETURNING clause.
(see
http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING)
So probably I'm doing something wrong. Can anyone tell me what?
Thanks,
Bart
In case it matters:
CREATE TABLE "public"."billing_errors_new" (
"billingid" INTEGER NOT NULL,
"errortypeid" INTEGER NOT NULL,
CONSTRAINT "billing_errors_new_billingid_fkey" FOREIGN KEY ("billingid")
REFERENCES "public"."billing"("id")
ON DELETE CASCADE
ON UPDATE CASCADE
DEFERRABLE
INITIALLY DEFERRED,
CONSTRAINT "billing_errors_new_errortypeid_fkey" FOREIGN KEY ("errortypeid")
REFERENCES "public"."billing_error_types"("id")
ON DELETE NO ACTION
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH (fillfactor = 100, OIDS = FALSE);
CREATE UNIQUE INDEX "billing_errors_new_unq" ON "public"."billing_errors_new"
USING btree ("billingid", "errortypeid")
WITH (fillfactor =100);
billingid errortypeid
118075 1
118076 1
118077 1
118078 1
213774 4
336717 4
349906 4
[SQL] 8.0.1 to 8.0.13 upgrade added 15% lack of time of query execution
Hello all, I beg your pardon if I do not know some thing, but I was disappointed after taken an upgrade from 8.0.1 to 8.0.13, now the query, that I had optimized so far to run for 92 sec on 8.0.1, takes 106 seconds on 8.0.13. The plan seems to stay unmodified, except statistics used for its construction, but one nested loop's actual time was largely increased: [For 8.0.13] Nested Loop Left Join (cost=113409.50..137207.88 rows=12214 width=146) (actual time=37839.806..104026.224 rows=26139 loops=1) [For 8.0.1] Nested Loop Left Join (cost=116564.46..119674.74 rows=1597 width=146) (actual time=32120.292..90484.361 rows=26139 loops=1) What I have done upgrading: 1. As rel.notes says I don't need dump/restore to perform my upgrade, even initdb is not needed for me, as I don't want to add security enhancements for now. 2. Compiled 8.0.13 from sources and have installed to a separate location (/usr/local/pgsql instead of old /usr/bin, /usr/lib,..) 3. chown-ed all stuff to postgres in /usr/local/pgsql 4. run postmaster with the old data directory specified. 5. reindexed database. Both plans with queries echoed are attached to this email. Will appreciate any help in navigating me to the right way. With best regards, Aleksandr. Timing is on. explain analyze select log_rec_id, log_date, log_uid, log_name, array_accum(trim(trailing ' ' from gh_name)) as groups from ( select distinct on (log_rec_id, start_rec_id, fin_rec_id, gm_rec_id) log_rec_id, log_date, log_uid, log_name, start_rec_id, start_date, start_action, start_uid, start_name, fin_rec_id, fin_date, fin_action, fin_uid, fin_name, gm_rec_id, gm_date, gm_gid, gm_uid, gh.rec_id as gh_rec_id, gh.date as gh_date, gh.action as gh_action, gh.gid as gh_gid, gh.name as gh_name from ( select * from group_history where action <> 1 ) as gh right join ( select log_rec_id, log_date, log_uid, log_name, start_rec_id, start_date, start_action, start_uid, start_name, fin_rec_id, fin_date, fin_action, fin_uid, fin_name, max(gm_rec_id) as gm_rec_id, max(gm_date) as gm_date, gm_gid, gm_uid from ( select luid.*, gmh.rec_id as gm_rec_id, gmh.date as gm_date, gmh.gid as gm_gid, gmh.uid as gm_uid, gmh.action as gm_action from group_member_history as gmh right join ( select distinct on (cuh.log_rec_id, cuh.start_rec_id) cuh.*, duh.* from ( select rec_id as fin_rec_id, date as fin_date, action as fin_action, uid as fin_uid, name as fin_name from "user_history" where 1 = 1 and action <> 0 ) as duh right join ( select distinct on (log.rec_id) log.rec_id as log_rec_id, log.date as log_date, log.uid as log_uid, log.name as log_name, uh.rec_id as start_rec_id, uh.date as start_date, uh.action as start_action, uh.uid as start_uid, uh.name as start_name from ( select * from "user_history" where 1 = 1 and action <> 1 order by date ) as uh right join log_example_3 as log on log.name = uh.name and uh.date <= log.date order by log.rec_id, start_date desc ) as cuh on cuh.start_uid = duh.fin_uid and duh.fin_date > cuh.start_date and duh.fin_date <= cuh.log_date order by cuh.log_rec_id, cuh.start_rec_id, duh.fin_rec_id ) as luid on gmh.uid = luid.start_uid
Re: [SQL] Use of delete...returning in function problem
Bart Degryse wrote: I'm trying to use a delete statement with returning clause in a function: FOR rec IN ( delete from billing_errors_new where errortypeid IN (1,2) returning *) LOOP I get following error though: ERROR: syntax error at or near "delete" at character 4 QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) returning *) CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5 According to the manual (I think) it should be possible: I think it's just the brackets () - plpgsql's parser isn't terribly sophisticated. This works for me, but with brackets doesn't. BEGIN; CREATE TEMPORARY TABLE test1 (a integer, b text); INSERT INTO test1 SELECT generate_series(1,100) AS a, 'text for b'; CREATE FUNCTION testdel() RETURNS integer AS $$ DECLARE n integer; r RECORD; BEGIN n := 0; FOR r IN DELETE FROM test1 WHERE a % 10 = 1 RETURNING * LOOP n := n + 1; END LOOP; RETURN n; END; $$ LANGUAGE plpgsql; SELECT testdel(); ROLLBACK; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Cast on character columns in views
--- "Luiz K. Matsumura" <[EMAIL PROTECTED]> wrote: > When I drop the view first , and then create again the view (in a > separated transaction), now the command works! (this is a bug?) Well according to the manual, it is working as it is intended to work: http://www.postgresql.org/docs/8.2/interactive/sql-createview.html I've created large SQL scripts that employ: DROP VIEW IF EXITS viewname; CREATE VIEW ... Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Cast on character columns in views
--- "Luiz K. Matsumura" <[EMAIL PROTECTED]> wrote: > Ugh, I'm totally crazy with this views > I'm using pgadmin with postgres, when I clink on "view the data of > selected object" button all works fine. > But when I open a query tool window and do: > > SELECT * FROM view1; > Now, again type1 column returns as bpchar. > But if I do: This might be a good question to ask on the PGAdmin mailing list or even try using the latest version of PGAdmin. Perhaps this is something that PGAdmin is doing rather than postgresql. If you open psql -U your_db_user -d your_database_name. and type: \d schema_name.view_name was does it show as the definition of the column. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Use of delete...returning in function problem
Amazing what a bracket can do :) Thanks for the help. >>> Richard Huxton <[EMAIL PROTECTED]> 2007-09-04 12:45 >>> Bart Degryse wrote: > I'm trying to use a delete statement with returning clause in a function: > FOR rec IN ( > delete from billing_errors_new where errortypeid IN (1,2) returning *) > LOOP > I get following error though: > ERROR: syntax error at or near "delete" at character 4 > QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) > returning *) > CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5 > > According to the manual (I think) it should be possible: I think it's just the brackets () - plpgsql's parser isn't terribly sophisticated. This works for me, but with brackets doesn't. BEGIN; CREATE TEMPORARY TABLE test1 (a integer, b text); INSERT INTO test1 SELECT generate_series(1,100) AS a, 'text for b'; CREATE FUNCTION testdel() RETURNS integer AS $$ DECLARE n integer; r RECORD; BEGIN n := 0; FOR r IN DELETE FROM test1 WHERE a % 10 = 1 RETURNING * LOOP n := n + 1; END LOOP; RETURN n; END; $$ LANGUAGE plpgsql; SELECT testdel(); ROLLBACK; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] How to influence the planner
On 8/31/07, Richard Ray <[EMAIL PROTECTED]> wrote: > Changing to enable_seqscan = on does solve this problem, thanks > Is there some method of crafting a query that will assert my wishes to the > planner > When is enable_seqscan = off appropriate enable_xxx = off are troubleshooting tools. They override the query planner. For instance, I had a query that was running slow, and using set enable_nestloop=off allowed the query to run fast. However, using explain analyze I could see that the estimated number of rows was off. Analyze didn't fix it, so I increased the stats target for the column I was working with, reanalyzed, and voila, the query ran fine with nestloop=on. So, enable_xxx=off is normally only appropriate when troubleshooting an issue, not as a fix all. That's doubly true for enable_seqscan=off. If you do have a query that nothing else seems to work on it, you can set one of the enable_xxx settings off for that connection only and not worry about messing up all the other sessions connecting to your db. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Partial index on boolean - Sometimes fails to index scan
Richard Huxton provided the answer: It's a prepared query-plan, which means it can't plan to use the index because the next EXECUTE might have reconciled=true. Bryce Nesbitt wrote: ...Which is all good. But the Hibernate version of query still takes several seconds, and still appears in my pg_log slow query log: LOG: duration: 2248.662 ms statement: EXECUTE C_51443 [PREPARE: select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=$1 )and(vehicleeve0_.RECONCILED=$2 )]
Re: [SQL] Cast on character columns in views
Richard Broersma Jr <[EMAIL PROTECTED]> writes: > --- "Luiz K. Matsumura" <[EMAIL PROTECTED]> wrote: >> But when I open a query tool window and do: >> SELECT * FROM view1; >> Now, again type1 column returns as bpchar. > This might be a good question to ask on the PGAdmin mailing list or even try > using the latest > version of PGAdmin. Perhaps this is something that PGAdmin is doing rather > than postgresql. The seeming ordering dependency certainly sounds like it might be a client-side bug --- something failing to keep straight which typmod goes with which column, maybe? There are some backend-side issues with simply not being able to tell the difference between null::bpchar and null::char(3) ... but for any given view definition, it's pretty hard to see how the order of selecting the columns would matter. A client-side bug seems a bit more likely. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Trigger to change different row in same table
I want to write a trigger that updates a different row on the same table. It's pretty basic: before the any row in his table updated I want to set a only row that has the value true to false. I keep getting this error: SQL statement "update theirry.articles set master_featured = false where master_featured = true" PL/pgSQL function "master_featured_maintenance" line 4 at SQL statement My basic trigger: CREATE OR REPLACE FUNCTION theirry.master_featured_maintenance() RETURNS TRIGGER AS $master_featured_maintenance$ DECLARE master_feature boolean; BEGIN update theirry.articles set master_featured = false where master_featured = true; END; $master_featured_maintenance$ LANGUAGE plpgsql; CREATE TRIGGER master_featured_maintenance BEFORE INSERT OR UPDATE ON theirry.articles FOR EACH ROW EXECUTE PROCEDURE theirry.master_featured_maintenance(); Thanks in advance, J ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Trigger to change different row in same table
chester c young wrote: > how are you preventing recursion? > > That could be the problem, suggestions? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Use of delete...returning in function problem
Richard Huxton <[EMAIL PROTECTED]> writes: > I think it's just the brackets () - plpgsql's parser isn't terribly > sophisticated. It's not plpgsql's fault --- you'll get the same result if you put parentheses around a DELETE command at the SQL command line. regression=# (delete from fool); ERROR: syntax error at or near "delete" LINE 1: (delete from fool); ^ The OP may be used to putting parens into his FOR loops because it works with SELECT: regression=# (select * from zz1); f1 | f2 | f3 ++ (0 rows) The difference is that SELECT can be put into larger groupings (eg UNIONs) so it has to be parenthesiz-able. If we ever considered supporting DELETE RETURNING as a component of larger queries, this syntax difference would likely go away. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to influence the planner
Thanks guys Lesson learned On Tue, 4 Sep 2007, Scott Marlowe wrote: On 8/31/07, Richard Ray <[EMAIL PROTECTED]> wrote: Changing to enable_seqscan = on does solve this problem, thanks Is there some method of crafting a query that will assert my wishes to the planner When is enable_seqscan = off appropriate enable_xxx = off are troubleshooting tools. They override the query planner. For instance, I had a query that was running slow, and using set enable_nestloop=off allowed the query to run fast. However, using explain analyze I could see that the estimated number of rows was off. Analyze didn't fix it, so I increased the stats target for the column I was working with, reanalyzed, and voila, the query ran fine with nestloop=on. So, enable_xxx=off is normally only appropriate when troubleshooting an issue, not as a fix all. That's doubly true for enable_seqscan=off. If you do have a query that nothing else seems to work on it, you can set one of the enable_xxx settings off for that connection only and not worry about messing up all the other sessions connecting to your db. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] work hour calculations
Hello All, SELECT notification_time, finished_time, sum(finished_time - notification_time) as actual FROM log GROUP BY notification_time, finished_time; gives me: notification_time| finished_time | actual ++- 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 2 days 15:20:00 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 1 day 18:50:00 How can write a query to calculate the duration using custom work hours which is Monday 7am / Friday 5pm? The result I'm expecting for the above to be notification_time| finished_time | actual ++- 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 00:20:00 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00 Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] work hour calculations
correction: > The result I'm expecting for the above to be > >notification_time| finished_time | actual > ++- 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 01:20:00 > 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00 > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] work hour calculations
am Wed, dem 05.09.2007, um 13:45:46 +1000 mailte novice folgendes:
> Hello All,
>
> SELECT notification_time, finished_time, sum(finished_time -
> notification_time) as actual
> FROM log
> GROUP BY notification_time, finished_time;
>
> gives me:
>
>notification_time| finished_time | actual
> ++-
> 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 2 days 15:20:00
> 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 1 day 18:50:00
>
>
> How can write a query to calculate the duration using custom work
> hours which is Monday 7am / Friday 5pm?
>
> The result I'm expecting for the above to be
>
>notification_time| finished_time | actual
> ++-
> 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 00:20:00
> 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00
You can write a function. Calculate for every day between
notification_time and finished_time this timestamps for every day. I
mean, for instance your first row:
2007-07-06 15:50:00+10 2007-07-07 00:00:00+10
2007-07-07 00:00:00+10 2007-07-08 00:00:00+10
2007-07-08 00:00:00+10 2007-07-09 00:00:00+10
2007-07-09 00:00:00+10 2007-07-09 07:20:00+10
Now check, if the first timestamp are a working day (select
extract('dow' from '2007-07-06 15:50:00+10'::timestamptz)). If so, than
calculate the working-time and adds all.
A little function for you:
<--- cut
create or replace function intersect_time (IN start timestamptz, IN stop
timestamptz, IN w_start timestamptz, IN w_end timestamptz, OUT duration
interval) as $$
declare
_s1 alias for $1;
_e1 alias for $2;
_s2 alias for $3;
_e2 alias for $4;
_start timestamptz;
_endtimestamptz;
begin
if _s1 < _s2 then
_start := _s2;
else
_start := _s1;
end if;
if _e1 < _e2 then
_end := _e1;
else
_end := _e2;
end if;
if _start < _end then
duration := _end - _start;
else
duration := '0'::interval;
end if;
return;
end;
$$language plpgsql;
--- cut
A simple test:
Only the first and the last are working days, so we call the function
for this rows:
test=*# select intersect_time('2007-07-06 15:50:00+10'::timestamptz,'2007-07-07
00:00:00+10'::timestamptz, '2007-07-06 07:00:00+10'::timestamptz, '2007-07-06
17:00:00+10'::timestamptz);
intersect_time
01:10:00
(1 row)
test=*# select intersect_time('2007-07-09 00:00:00+10'::timestamptz,'2007-07-09
07:10:00+10'::timestamptz, '2007-07-09 07:00:00+10'::timestamptz, '2007-07-09
17:00:00+10'::timestamptz);
intersect_time
00:10:00
(1 row)
test=*# select '01:10:00'::interval + '00:10:00'::interval;
?column?
--
01:20:00
(1 row)
Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
