Re: [SQL] Help me do a LOOP
Calin Meze wrote: I need to test each record of a cursor for some requirements, something like this [snip] -- the problem is that I do not know how to make the while loop execute until the last record "WHILE (still in the cursor)"... So I need something like Can anyone help me with this? It really seems silly... but I cannot get to solve this, I do not know where to look for syntax like this or tutorials like this. Assuming this is plpgsql you are probably looking for the FOUND pseudo-variable. See Ch 36.6.6 Obtaining the Result Status You don't actually need to bother with a cursor in plpgsql, just loop over a SELECT (Ch 36.7.4 Looping Through Query Results). That basically *is* a cursor internally. -- Richard Huxton Archonet Ltd ---(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] Commiting after certain no of rows have been deleted
Smita Mahadik wrote: Hi, In my application I m deleteing large no of rows from table based on certain condition. This takes lot of time and if sometimes my application fails it starts all over again...since the coomit is done at the end of transactions. Is there a way i can do commit when certain no of rows have been deleted? For eg if i need to delete 2 million rows i should be able to commit after say 10,000 rows. No - the whole point of the transaction is it all works or none of it does. But, if you have a suitable ID/Name/timestamp/other varying column you can break it into smaller chunks: DELETE FROM contacts WHERE surname LIKE 'A%'; DELETE FROM contacts WHERE surname LIKE 'B%'; ...etc... don't forget a final "full" delete to catch anything you miss DELETE FROM contacts; Ideally you'll have something with an index on it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Help on a complex query (avg data for day of the week)
Matthew Smith wrote:
I want to form a query that returns the average total usage for each day of
the week, eg:
[snip]
To get this info, I am using the following query:
select dow as day, sum(sum_data)/count(dow) as avg_usage from
(select extract('dow' from date_trunc('day', time)) as dow, sum(data) as
sum_data
from datalog where time >= '2005-09-11' and time <= '2005-09-25' group by dow)
as avg_data_per_day group by day;
This works well, assuming that there is at least one entry in the table for
each day in the time period.
The problem comes when there are days where no data is logged.
1. Calculate how many data-points each day represents
2. Sum the days you do have data for
3. Left-join #1 to #2 so you can calculate the average.
I'm guessing for #1 you'd rather not have 7 UNIONs, so you might find
generate_series() a useful function. See Ch 9.18. Set Returning Functions.
HTH
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Problem obtaining MAX values FROM TABLE
On Fri, Dec 16, 2005 at 03:42:34PM -, Michael Farewell wrote: > I need to write a query which returns each company together with the > highest product_count and its associated product type so the result should > look like this: There are a few ways to do this, a couple of which were mentioned recently in another thread. One is to use PostgreSQL's non-standard DISTINCT ON: SELECT DISTINCT ON (company_name) b_id, company_name, product_count, product_type FROM foo ORDER BY company_name, product_count DESC; Here's another way that uses standard SQL syntax: SELECT b_id, company_name, product_count, product_type FROM foo AS a WHERE NOT EXISTS ( SELECT 1 FROM foo AS b WHERE b.company_name = a.company_name AND b.product_count > a.product_count ) ORDER BY company_name; These queries aren't quite the same, however: the first will return exactly one row per company, while the second will return multiple rows per company if multiple rows have the maximum product count. For example, suppose you have this data, where two rows for company 1 have that company's maximum product count: b_id | company_name | product_count | product_type --+--+---+-- 1 | company 1| 1 | a 1 | company 1| 2 | b 1 | company 1| 2 | c 2 | company 2| 3 | d 2 | company 2| 4 | e The first query (using DISTINCT ON) will return the following, picking the row for company 1 based on whichever row among the maximums is ordered first (which you can control with ORDER BY; otherwise the row chosen will be indeterminate): b_id | company_name | product_count | product_type --+--+---+-- 1 | company 1| 2 | b 2 | company 2| 4 | e The second query will return both of the maximum rows for company 1: b_id | company_name | product_count | product_type --+--+---+-- 1 | company 1| 2 | b 1 | company 1| 2 | c 2 | company 2| 4 | e -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Does VACUUM reorder tables on clustered indices
[EMAIL PROTECTED] ("Jim C. Nasby") writes:
> On Wed, Dec 21, 2005 at 12:34:12AM +0100, [EMAIL PROTECTED] wrote:
>> Hi,
>>
>> Utilize CLUSTER; (after vacuum) to reorder the data.
>
> Why would you vacuum when cluster is just going to wipe out the dead
> tuples anyway?
There is one reason to VACUUM before running CLUSTER...
That is that VACUUM will be *guaranteed* to draw all the pages into memory.
Subsequently, you can be certain that the pages are in cache, and that
the CLUSTER should need to do minimal I/O to read data into memory.
If I'm considering clustering the Slony-I "sl_log_1" table, forcing it
into memory *is* something I'll consider doing in order to minimize
the time that would-be writers are blocked from writing...
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/multiplexor.html
Never criticize anybody until you have walked a mile in their shoes,
because by that time you will be a mile away and have their shoes.
-- email sig, Brian Servis
---(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
Re: [SQL] Help on a complex query (avg data for day of the week)
Richard (and list),
Thanks for the help! More below:
On Wed, 21 Dec 2005 09:04 pm, Richard Huxton wrote:
> Matthew Smith wrote:
> > I want to form a query that returns the average total usage for each day
> > of the week, eg:
>
> [snip]
>
> > To get this info, I am using the following query:
> >
> > select dow as day, sum(sum_data)/count(dow) as avg_usage from
> > (select extract('dow' from date_trunc('day', time)) as dow, sum(data) as
> > sum_data
> > from datalog where time >= '2005-09-11' and time <= '2005-09-25' group by
> > dow) as avg_data_per_day group by day;
> >
> > This works well, assuming that there is at least one entry in the table
> > for each day in the time period.
> >
> > The problem comes when there are days where no data is logged.
>
> 1. Calculate how many data-points each day represents
> 2. Sum the days you do have data for
> 3. Left-join #1 to #2 so you can calculate the average.
>
> I'm guessing for #1 you'd rather not have 7 UNIONs, so you might find
> generate_series() a useful function. See Ch 9.18. Set Returning Functions.
Sadly I am using 7.3, it seems that generate_series() is 8.0 and later. I am
not sure what you meant by "Calculate how many data-points each day
represents", but your mention of unions gave me an idea. It's nasty, but it
will work for what I am trying to do (sorry if it is what you meant):
I can use unions to make a list of dates and left join them into my query to
get the missing days in. This would be the same as left joining in the result
from generate_series(), but it is not as tidy.
I will make the list using something like this:
select '2005-09-11'::timestamp as date UNION select '2005-09-12'::timestamp as
date UNION select '2005-09-13'::timestamp as date UNION etc.
It's nasty, but it will hold until I can migrate to 8.1. Thanks again for the
help!
Cheers,
Matthew Smith
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help on a complex query (avg data for day of the week)
On Thu, Dec 22, 2005 at 11:59:24AM +1100, Matthew Smith wrote: > Sadly I am using 7.3, it seems that generate_series() is 8.0 and later. Yes, but it's easily written in PL/pgSQL for earlier versions. Example: CREATE FUNCTION generate_series(integer, integer) RETURNS SETOF integer AS ' DECLARE i integer; BEGIN FOR i IN $1 .. $2 LOOP RETURN NEXT i; END LOOP; RETURN; END; ' LANGUAGE plpgsql VOLATILE STRICT; -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] lo function changed in PostgreSQL 8.1.1 (ask again)
From contrib/lo I found that it has something difference between old and new version of PostgreSQL. And I'm sure that I already tick on Large Object (lo) option when I install. How can I manage on difference function? :::New Version::: DOMAIN lo AS pg_catalog.oid; FUNCTION lo_oid(lo) RETURNS pg_catalog.oid AS 'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT IMMUTABLE; FUNCTION lo_manage() RETURNS pg_catalog.trigger AS '$libdir/lo' LANGUAGE C; :::Old Version::: FUNCTION lo_in(cstring) RETURNS lo AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; FUNCTION lo_out(lo) RETURNS cstring AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; TYPE lo ( INTERNALLENGTH = 4, EXTERNALLENGTH = variable, INPUT = lo_in, OUTPUT = lo_out); FUNCTION lo_oid(lo) RETURNS oid AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; FUNCTION oid(lo) RETURNS oid AS '$libdir/lo', 'lo_oid' LANGUAGE C IMMUTABLE STRICT; CAST (lo as oid) WITH FUNCTION oid(lo) AS IMPLICIT; FUNCTION lo(oid) RETURNS lo AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT; CAST (oid as lo) WITH FUNCTION lo(oid) AS IMPLICIT; FUNCTION lo_manage() RETURNS trigger AS '$libdir/lo' LANGUAGE C;
Re: [SQL] Help on a complex query (avg data for day of the week)
Michael, Thanks heaps for that! Cheers, Matt Smith On Thu, 22 Dec 2005 01:37 pm, Michael Fuhr wrote: > On Thu, Dec 22, 2005 at 11:59:24AM +1100, Matthew Smith wrote: > > Sadly I am using 7.3, it seems that generate_series() is 8.0 and later. > > Yes, but it's easily written in PL/pgSQL for earlier versions. > Example: > > CREATE FUNCTION generate_series(integer, integer) > RETURNS SETOF integer AS ' > DECLARE > i integer; > BEGIN > FOR i IN $1 .. $2 LOOP > RETURN NEXT i; > END LOOP; > RETURN; > END; > ' LANGUAGE plpgsql VOLATILE STRICT; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Does VACUUM reorder tables on clustered indices
Chris Browne wrote:
> [EMAIL PROTECTED] ("Jim C. Nasby") writes:
> > On Wed, Dec 21, 2005 at 12:34:12AM +0100, [EMAIL PROTECTED] wrote:
> >> Hi,
> >>
> >> Utilize CLUSTER; (after vacuum) to reorder the data.
> >
> > Why would you vacuum when cluster is just going to wipe out the dead
> > tuples anyway?
>
> There is one reason to VACUUM before running CLUSTER...
>
> That is that VACUUM will be *guaranteed* to draw all the pages into memory.
>
> Subsequently, you can be certain that the pages are in cache, and that
> the CLUSTER should need to do minimal I/O to read data into memory.
>
> If I'm considering clustering the Slony-I "sl_log_1" table, forcing it
> into memory *is* something I'll consider doing in order to minimize
> the time that would-be writers are blocked from writing...
Why don't you just do SELECT * FROM tab WHERE col != 'lkjasdflkjadsf'.
That should pull things into memory without the VACUUM overhead.
--
Bruce Momjian| http://candle.pha.pa.us
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
