[SQL] Re: returning the number of rows output by a copy command from a function
On 2013-01-16, James Sharrett wrote: > The problem I have is that I get nothing back when the COPY is run inside > the function other than what I explicitly return from the function so I > don't have anything to parse. It's odd that the record count in the > function is treated differently than from sql query in GET DIAGNOSTIC > since the format and information in the string (when run outside of the > function) are exactly the same. look into "get diagnostics" -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] number of values updated per column
Hi, I'd like to know, per column, how many values were changed by my query. I have a working example, but i am curious what you people think about it. Is it efficient? I have to make a self join, but i don't see a faster way. Here's the example: - drop table if exists tab1 ; create table tab1(id serial primary key, a integer, b integer, c integer); insert into tab1 (a,b,c) select x*random(), x*random(), x*random() from generate_series(0,100) foo(x); with foo as ( update tab1 set a=case when tab1.a >= 60 then -1 else tab1.a end , b=case when tab1.b >= 60 then -1 else tab1.b end , c=case when tab1.c >= 60 then -1 else tab1.c end from tab1 old where old.id=tab1.id returning case when tab1.a != old.a then 1 else 0 end as a_upd , case when tab1.b != old.b then 1 else 0 end as b_upd , case when tab1.c != old.c then 1 else 0 end as c_upd ) select 'a' as fieldname, sum(a_upd) as updates from foo union all select 'b' as fieldname, sum(b_upd) as updates from foo union all select 'c' as fieldname, sum(c_upd) as updates from foo - Cheers, WBL -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
Re: [SQL] number of values updated per column
How about separating count query from update statement. I found a bit performance improvement from your example. with 10 rows, fastest time in 10 times try yours: 989.679 ms mine: 719.739 ms query --- (same DDL, DML) WITH cnt AS ( SELECT count(CASE WHEN tab1.a >= 60 THEN 1 END) AS a_count, count(CASE WHEN tab1.b >= 60 THEN 1 END) AS b_count, count(CASE WHEN tab1.c >= 60 THEN 1 END) AS c_count FROM tab1 ), upd AS ( UPDATE tab1 SET a = CASE WHEN tab1.a >= 60 THEN -1 ELSE tab1.a END, b = CASE WHEN tab1.b >= 60 THEN -1 ELSE tab1.b END, c = CASE WHEN tab1.c >= 60 THEN -1 ELSE tab1.c END ) select a_count, b_count, c_count from cnt ; On 2013/01/18, at 2:36, Willy-Bas Loos wrote: > Hi, > > I'd like to know, per column, how many values were changed by my query. > I have a working example, but i am curious what you people think about it. > Is it efficient? I have to make a self join, but i don't see a faster way. > > > Here's the example: > - > drop table if exists tab1 ; > create table tab1(id serial primary key, a integer, b integer, c integer); > insert into tab1 (a,b,c) > select x*random(), x*random(), x*random() > from generate_series(0,100) foo(x); > > with foo as ( > update tab1 set > a=case when tab1.a >= 60 then -1 else tab1.a end > , b=case when tab1.b >= 60 then -1 else tab1.b end > , c=case when tab1.c >= 60 then -1 else tab1.c end > from tab1 old > where old.id=tab1.id > returning > case when tab1.a != old.a then 1 else 0 end as a_upd > , case when tab1.b != old.b then 1 else 0 end as b_upd > , case when tab1.c != old.c then 1 else 0 end as c_upd > ) > select 'a' as fieldname, sum(a_upd) as updates from foo > union all > select 'b' as fieldname, sum(b_upd) as updates from foo > union all > select 'c' as fieldname, sum(c_upd) as updates from foo > - > > Cheers, > > WBL > -- > "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
[SQL] Aggregate over a linked list
Hello! I trade futures contracts and I have a PostgreSQL 9.2 database that keeps track of all of my trading activity. The table includes columns for the futures contract, the entry and exit dates and the profit for that particular trade. Now, futures contracts expire, so within a trade being indicated by my rules, I may need to "roll" contracts to the next contract to avoid contract expiration. Therefore I can end up with multiple actual trades in my table that are linked by the dates. e.g. If the contract is SH12 (S = soybeans and H12 = March 2012 expiration) contract entry_date exit_date profit --- SH12 2012-1-102012-2-27 500.00 SM12 2012-2-272012-3-30 200.00 While these are the actual exchange executed trades, I'd like to reduce this into a single row like (linked by the "S" and then exit_date = entry_date): contract entry_date exit_date profit --- S 2012-1-102012-3-30 700.00 I've gone round and round in my head, google, SQL Cookbook, etc. trying to figure out how to do this. Can anyone provide any pointers on how to do this? Thanks and best regards! Mark
Re: [SQL] Aggregate over a linked list
Did you try: select substring(contract from 1 for 1), min(entry_date), max(entry_date), sum(profit) from contract_table group by 1; Venky Kandaswamy Principal Engineer, Adchemy Inc. 925-200-7124 From: [email protected] [[email protected]] on behalf of M Lubratt [[email protected]] Sent: Thursday, January 17, 2013 3:19 PM To: [email protected] Subject: [SQL] Aggregate over a linked list Hello! I trade futures contracts and I have a PostgreSQL 9.2 database that keeps track of all of my trading activity. The table includes columns for the futures contract, the entry and exit dates and the profit for that particular trade. Now, futures contracts expire, so within a trade being indicated by my rules, I may need to "roll" contracts to the next contract to avoid contract expiration. Therefore I can end up with multiple actual trades in my table that are linked by the dates. e.g. If the contract is SH12 (S = soybeans and H12 = March 2012 expiration) contract entry_date exit_date profit --- SH12 2012-1-102012-2-27 500.00 SM12 2012-2-272012-3-30 200.00 While these are the actual exchange executed trades, I'd like to reduce this into a single row like (linked by the "S" and then exit_date = entry_date): contract entry_date exit_date profit --- S 2012-1-102012-3-30 700.00 I've gone round and round in my head, google, SQL Cookbook, etc. trying to figure out how to do this. Can anyone provide any pointers on how to do this? Thanks and best regards! Mark
