Hello,
I'm writing a function in PL/pgSQL and I would like to know if there
is a method to get the number of records in a result set, after a
select query, without executing the same query using COUNT(*).
Thanks.
Regards,
Eugenio.
---(end of
Adam,
I don't get the correct row counts when running this SQL.
It seems to produce the correct count when there are no rows found,but not when
at least
one row is found (I get a much higher count than when running:
SELECT count(*) FROM table2 WHERE date BETWEEN.AND table1.id = n
,which is
Hello
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
regards
Pavel Stehule
On 30/01/2008, Eugenio Tacchini [EMAIL PROTECTED] wrote:
Hello,
I'm writing a function in PL/pgSQL and I would like to know if there
is a method to get the
On Wed, 2008-01-30 at 09:14 +0100, Eugenio Tacchini wrote:
Hello,
I'm writing a function in PL/pgSQL and I would like to know if there
is a method to get the number of records in a result set, after a
select query, without executing the same query using COUNT(*).
not sure what exactly you
Adam,
I just realised that issuing the SQL on one table produces the correct count.
SELECT sum(case when table2.date between '2007-07-13' and '2007-07-13' then 1
else 0
end) as sumx FROM table2 WHERE id = n;
This is working alright.
So the problem should lie in the last part:
from table2,
At 16.35 30/01/2008 +0800, Ow Mun Heng wrote:
not sure what exactly you mean, but perhaps this could help?
del_stime := timeofday();
execute del_qry;
del_etime := timeofday();
GET DIAGNOSTICS del_rows = ROW_COUNT;
This would get you the # of rows inserted into the destination
On Jan 30, 2008 11:35 AM, Raymond O'Donnell [EMAIL PROTECTED] wrote:
On 30/01/2008 11:27, Gregory Stark wrote:
In fact I think most of the features you'll look for examples of will be
from
the last 1-2 years. When 8.3 comes out people will be looking for whole
books
on XML
On 30/01/2008 11:27, Gregory Stark wrote:
In fact I think most of the features you'll look for examples of will be from
the last 1-2 years. When 8.3 comes out people will be looking for whole books
on XML functionality, tsearch implementations, etc, and there will be nothing
aside from the
Ow Mun Heng [EMAIL PROTECTED] writes:
rant
One of the worst aspect of PG is the documentation, or the lack of it in
terms of traditional house. The Manual is fine and all, but in most
cases, what I find that it lacks is actually examples. Either examples
to show what it a particular
On Wed, 2008-01-30 at 15:54 +0800, Ow Mun Heng wrote:
rant
One of the worst aspect of PG is the documentation, or the lack of it in
terms of traditional house. The Manual is fine and all, but in most
cases, what I find that it lacks is actually examples. Either examples
to show what it a
On Wed, 30 Jan 2008 11:27:20 +
Gregory Stark [EMAIL PROTECTED] wrote:
Ow Mun Heng [EMAIL PROTECTED] writes:
rant
One of the worst aspect of PG is the documentation, or the lack
of it in terms of traditional house. The Manual is fine and
all, but in most cases, what I find that it
On 30/01/2008 12:12, Ivan Sergio Borgonovo wrote:
Many things are already there in the VI Reference section but some
are not, especially in the V Server programming part.
+1
The Server Programming section is where we really need lots of examples.
Ray.
Joshua D. Drake wrote:
Tom Hart [EMAIL PROTECTED] wrote:
[...]
I find that the best way to get what you need, is to read the fine
manual from postgresql. Yes, its massive, unwieldy and in a lot of
ways counter-intuitive (to a newbie) but if you have the terminology
down you aren't going to
On Jan 29, 2008, at 23:42 , Ow Mun Heng wrote:
This is OT for this list and I don't have access to I-net (only email)
and I'm not subscribed to the Slony list.
To see your options for the slony1-general mailing list by email,
send a message to:
[EMAIL PROTECTED]
with the word `help'
On Jan 30, 2008 12:45 PM, Peter Wilson [EMAIL PROTECTED] wrote:
Has anyone else generated a Windows Help version of the manual?
We distribute it with PostgreSQL - it's just not integrated with the
pgAdmin help any more. You can even tell pgAdmin to use that if you
don''t wish to use the online
On Wed, 30 Jan 2008 11:27:20 +
Gregory Stark [EMAIL PROTECTED] wrote:
BTW examples are a sort of specification too. I wouldn't
underestimate their more formal value. So I think they should be part
of *the* reference documentation with example output as well.
They shouldn't be of the
Am Mittwoch, 30. Januar 2008 schrieb Raymond O'Donnell:
Isn't this the idea of the interactive online docs? People can add stuff
they find useful for others.
Well, not really, for better or worse. Each release, we take the comments and
either fold them into the main documentation or delete
Dave Page wrote:
On Jan 30, 2008 1:34 PM, Peter Wilson [EMAIL PROTECTED] wrote:
Dave Page wrote:
On Jan 30, 2008 12:45 PM, Peter Wilson [EMAIL PROTECTED] wrote:
Has anyone else generated a Windows Help version of the manual?
Is it only distributed with the Window distribution?
Michael Meskes wrote:
On Mon, Jan 14, 2008 at 10:57:45AM -0500, Tom Lane wrote:
I'm concerned about this too. We'll at least have to call this out as
an incompatibility in 8.3, and it seems like a rather unnecessary step
backwards.
Given that people seem to use this feature I'm more
On Jan 30, 2008 1:34 PM, Peter Wilson [EMAIL PROTECTED] wrote:
Dave Page wrote:
On Jan 30, 2008 12:45 PM, Peter Wilson [EMAIL PROTECTED] wrote:
Has anyone else generated a Windows Help version of the manual?
We distribute it with PostgreSQL - it's just not integrated with the
Dave Page wrote:
On Jan 30, 2008 12:45 PM, Peter Wilson [EMAIL PROTECTED] wrote:
Has anyone else generated a Windows Help version of the manual?
We distribute it with PostgreSQL - it's just not integrated with the
pgAdmin help any more. You can even tell pgAdmin to use that if you
Am Mittwoch, 30. Januar 2008 schrieb Peter Wilson:
Has anyone else generated a Windows Help version of the manual?
It can be built from the source code using the make htmlhelp target in
doc/src/sgml/. I don't know how to get from there to the final format,
though. I understand it is
Am Mittwoch, 30. Januar 2008 schrieb Ivan Sergio Borgonovo:
How/where is it possible to submit doc patches?
[EMAIL PROTECTED] -- The process is mostly the same as for normal
code. The Developer section of the web site gives you more information.
--
Peter Eisentraut
Hello,
Last night I was working realy hard (10 hours) while reinstalling some
servers in Freiburg and now I have a big problem/question to tables...
My customer had used PostgreSQL 7.4 and we have dumped all tables into
separated dumps because the tables are too big!!!
Formerly, it was the
Hello Suresh,
Suresh Gupta VG wrote:
Hi Team,
I have Solaris 9 machine with Pgsql 7.4 and want to upgrade to 8.2.5.
I don't want to disturb the first version now. I want to install
second one separately and need to test with my application. If it
works fine, I will activate only the
On Jan 30, 2008, at 12:18 AM, Sim Zacks wrote:
Anyway my suggestion to Sim is to read about each conference on the
respective conference websites:
http://www.postgresqlconference.org/why/
http://www.pgcon.org/2008/
I read those, I was just confused as to why there were 2
conferences on the
=?iso-8859-1?Q?H=E5kan_Jacobsson?= [EMAIL PROTECTED] writes:
I just realised that issuing the SQL on one table produces the correct count.
SELECT sum(case when table2.date between '2007-07-13' and '2007-07-13' then 1
else 0
end) as sumx FROM table2 WHERE id = n;
This is working alright.
vincent [EMAIL PROTECTED] writes:
In the manual yes, but I think there's definately a need for a howto
document, something that demonstrates how to handle typical database
functionality in PgSQL. Many of the people I've convinced to start using
PostgeSQL spend the first week or so asking me
Tom Lane wrote:
vincent [EMAIL PROTECTED] writes:
In the manual yes, but I think there's definately a need for a howto
document, something that demonstrates how to handle typical database
functionality in PgSQL. Many of the people I've convinced to start using
PostgeSQL spend the first week
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 30 Jan 2008 13:20:58 -0500
Tom Hart [EMAIL PROTECTED] wrote:
regards, tom lane
I agree that it would be useful as an introduction, but I have 4
years of mySQL experience (I know, I'm sorry) and I've been working
Hi,
I noticed the upcoming support for xml in 8.3:
Does anybody know if this allows updates, inserts,removes and renames of
nodes to a XML-document (as for Xindice)?
Regards
Sigurd Nes
---(end of broadcast)---
TIP 3: Have you checked our extensive
Tom Lane wrote:
vincent [EMAIL PROTECTED] writes:
Surely even a book that's a little out-of-date can serve fine for that
kind of introduction?
I guess the point is that using older books is the only option, there
simple are no uptodate books available. People who want to use a book (and
--- vincent [EMAIL PROTECTED] wrote:
On Wed, 30 Jan 2008 11:27:20 +
Gregory Stark [EMAIL PROTECTED] wrote:
BTW examples are a sort of specification too. I wouldn't
underestimate their more formal value. So I think they should be
part
of *the* reference documentation with example
Glyn Astill wrote:
More documentation would be nice, but surely it's more down to
getting the type of user base that write your average how to books?
The O'Reilly books seem to cover postgres quite nicely, however I've
only had a flick through in shops.
One thing's for sure, 2 months ago I
vincent wrote:
True, but that only works for experienced 'nerds' who get a kick out of
connecting dots. Joe Average want's a bit more assistance, a bit more
guidance.
Have you read the Tutorial section of the docs? What do you feel it is
missing? Can you contribute to it?
--
Alvaro
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 30 Jan 2008 13:55:12 -0500
Tom Hart [EMAIL PROTECTED] wrote:
I definitely think that the lists are one of the shining stars for
postgresql support. I've learned some good reference stuff from
online docs/google but the really tricky
I'm in need of an aggregate hash function. Something like select
md5_agg(someTextColumn) from (select someTextColumn from someTable order by
someOrderingColumn). I know that there is an existing MD5 function, but it
is not an aggregate. I have thought about writing a concat aggregate
function
vincent wrote:
True, but that only works for experienced 'nerds' who get a kick out of
connecting dots. Joe Average want's a bit more assistance, a bit more
guidance.
Have you read the Tutorial section of the docs? What do you feel it is
missing? Can you contribute to it?
Yes I have.
Joshua D. Drake wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 30 Jan 2008 13:55:12 -0500
Tom Hart [EMAIL PROTECTED] wrote:
I definitely think that the lists are one of the shining stars for
postgresql support. I've learned some good reference stuff from
online docs/google
Hi,
is is possible to create an expression index based on the date_trunc function?
Working with PostgreSQL 8.2, I'm trying to create an index using:
CREATE INDEX request_day_idx ON moksha_sm_request (date_trunc('day',
request_received));
I get the error message:
ERROR: functions in index
Hello everybody. What started as a question about Practical PostgreSQL
has ballooned into a project to create another documentation resource,
compiled entirely from mailing list archives. While discussing
documentation in the general list I realized that the resource I had
learned the most
I'm trying to replicate the use of Oracle's 'lag' and 'over partition
by' analytical functions in my query. I have a table (all_client_times)
such as:
client_id, datetime
122, 2007-05-01 12:00:00
122, 2007-05-01 12:01:00
455, 2007-05-01 12:02:00
455, 2007-05-01 12:03:00
455, 2007-05-01
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Willem Buitendyk
Sent: Wednesday, January 30, 2008 1:15 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Oracle Analytical Functions
I'm trying to replicate the use of Oracle's
and I would like to create a new view that takes the first table and
calculates the time difference in minutes between each row so that the
result is something like:
client_id,datetime, previousTime, difftime
122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1
455,2007-05-01 12:03:00,
On Wed, 30 Jan 2008 16:10:07 -0500
Tom Hart [EMAIL PROTECTED] wrote:
Hello everybody. What started as a question about Practical
PostgreSQL has ballooned into a project to create another
[snip]
I generally tend to write stuff that was not clear on my website.
How about something like this:
SELECT
client_id
, datetime
, lagged as previoustime
, datetime - lagged difftime
FROM (
SELECT
client_id
,datetime
,(SELECT MAX(datetime)
FROM all_client_times def
WHERE def.client_id = abc.client_id
AND
Ivan Sergio Borgonovo wrote:
On Wed, 30 Jan 2008 16:10:07 -0500
Tom Hart [EMAIL PROTECTED] wrote:
Hello everybody. What started as a question about Practical
PostgreSQL has ballooned into a project to create another
[snip]
I generally tend to write stuff that was not clear on my
Sigurd Nes wrote:
I noticed the upcoming support for xml in 8.3:
Does anybody know if this allows updates, inserts,removes and renames of
nodes to a XML-document (as for Xindice)?
No, it doesn't support that directly. I guess you could achieve it by using
XSLT.
--
Peter Eisentraut
Josh,
Myself and a small team of PostgreSQL contributors have started a new
community project for PostgreSQL Certification. It is just launching
but we wanted to get it out there so that people can join in on the
discussion now :).
Who else is in this? Have you talked to the Venezualan
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 30 Jan 2008 14:17:43 -0800
Josh Berkus [EMAIL PROTECTED] wrote:
Current broadcast members are:
Myself
Magnus
Robert
Chander (need to get him on the website)
Bruce has a pending invitation (which I didn't send yet)
I have not spoken with
create table data (
client_id integer,
datetime timestamp not null
);
create index data_client_id on data(client_id);
copy data from STDIN DELIMITER ',';
122,2007-05-01 12:00:00
122,2007-05-01 12:01:00
455,2007-05-01 12:02:00
455,2007-05-01 12:03:00
455,2007-05-01 12:08:00
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hey guys,
Myself and a small team of PostgreSQL contributors have started a new
community project for PostgreSQL Certification. It is just launching
but we wanted to get it out there so that people can join in on the
discussion now :).
For more
A Redhead wrote:
CREATE INDEX request_day_idx ON moksha_sm_request
(date_trunc('day', request_received));
I get the error message:
ERROR: functions in index expression must be marked IMMUTABLE
[...]
I'd be grateful if someone could point out what part of the statement
is not
[EMAIL PROTECTED] writes:
CREATE INDEX request_day_idx ON moksha_sm_request (date_trunc('day',
request_received));
...
I'd be grateful if someone could point out what part of the statement is not
IMMUTABLE or how I could mark my create index statement as being immutable.
Most implementations of md5 internally consist of 3 functions: md5_init -
which initializes internal context, md5_update - which accepts portions of
data and processes them and md5_final - which finalizes the hash and
releases the context. These roughly suit aggregate's internal functions
(SFUNC
Josh,
I have not spoken with SRA or the Venezualan folks but am more than
happy to have them involved.
OK, I'll get you some contact info.
--Josh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Hi all,
I'm new to PostGreSql.
http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql
Check out the link. I am starting out on a new personal project had zeroed
in on PostGreSql with Mono-ASP.NET as ideal for my needs, mainly owing to a
PostGreSql whitepaper.
Now,
On Jan 30, 2008 4:40 PM, Vyacheslav Kalinin [EMAIL PROTECTED] wrote:
Most implementations of md5 internally consist of 3 functions: md5_init -
which initializes internal context, md5_update - which accepts portions of
data and processes them and md5_final - which finalizes the hash and
Chander Ganesan wrote:
Jeremy Harris wrote:
Version:
PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20070418 (Red Hat 4.1.2-10)
We have one problematic table, which has a steady stream of entries
and a weekly mass-delete of ancient history. The bloat query from
Can you show us the goals of the PostgreSQL Certification ?
I always voted for the united PostgreSQL Certification program
(amin, developer) we could promote with the help of commercial companies.
In my opinion, common certificate, valid in all countries will be much more
useful than buttons.
I tried this function but it keeps returning an error such as:
ERROR: invalid input syntax for integer: 2007-05-05 00:34:08
SQL state: 22P02
Context: PL/pgSQL function lagfunc line 10 at assignment
I checked and there are no datetime values in the client_id field
anywhere in my table
Thanks Reece,
I got this to work for me. The only problem was with the ORDER BY
clause which did not seem to work properly. I took it out and instead
used a sorted view for the data table.
Cheers,
Willem
Reece Hart wrote:
create table data (
client_id integer,
datetime timestamp
Found the error:
client_id := thisrow.datetime;
should be
client_id := thisrow.client_id;
All works well now,
Thanks very much,
Willem
Willem Buitendyk wrote:
I tried this function but it keeps returning an error such as:
ERROR: invalid input syntax for integer: 2007-05-05 00:34:08
SQL
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 30 Jan 2008 15:11:05 -0800
Swaminathan Saikumar [EMAIL PROTECTED] wrote:
Hi all,
I'm new to PostGreSql.
http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql
The cons of PostgreSql Hosting
*
I tried this function but it keeps returning an error such as:
ERROR: invalid input syntax for integer: 2007-05-05 00:34:08
SQL state: 22P02
Context: PL/pgSQL function lagfunc line 10 at assignment
Whoops, this line:
client_id := thisrow.datetime;
Should be:
client_id :=
--- On Wed, 1/30/08, Swaminathan Saikumar [EMAIL PROTECTED] wrote:
The cons of PostgreSql Hosting
* Performance considerations: Inserts and Updates into the
PostgreSql database is much slower compared to MySql. PostgreSql
hosting thus might slow down the display of the web page online.
...
Swaminathan Saikumar wrote:
Hi all,
I'm new to PostGreSql.
http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql
What a wonderful article - it's almost worth keeping a copy. It's so bad
it's difficult to know where to start. I think my favourite has to be :
+
On Jan 30, 2008 5:11 PM, Swaminathan Saikumar [EMAIL PROTECTED] wrote:
Hi all,
I'm new to PostGreSql.
http://searchyourwebhost.com/web-hosting/articles/insight-database-hosting-using-sql
I just skimmed through that page and honestly, it's wrong on LOTS of
counts, again and again. For
Josh Berkus wrote:
Josh,
Myself and a small team of PostgreSQL contributors have started a new
community project for PostgreSQL Certification. It is just launching
but we wanted to get it out there so that people can join in on the
discussion now :).
Who else is in this? Have you talked to
Josh Berkus wrote:
Josh,
Myself and a small team of PostgreSQL contributors have started a new
community project for PostgreSQL Certification. It is just launching
but we wanted to get it out there so that people can join in on the
discussion now :).
Who else is in this? Have you talked to
On Jan 30, 2008, at 6:22 PM, Richard Broersma Jr wrote:
If you don't get too much feed back on this subject, just remember
that topics like this come up frequently to the point of list
member exhaustion. You can find such discussions if you search
the list archive.
Too true. There's
Thanks everyone. After all the good things I heard about
Postgres, I was surprised to see this article; and the point on
storage concerned me.
I am glad to see that the article was wrong, not only on the storage engine
count, but also on others.
Thanks for the feedback.
On 1/30/08, Erik Jones
On Jan 30, 2008, at 5:11 PM, Swaminathan Saikumar wrote:
Hi all,
I'm new to PostGreSql.
http://searchyourwebhost.com/web-hosting/articles/insight-database-
hosting-using-sql
Check out the link. I am starting out on a new personal project
had zeroed in on PostGreSql with Mono-ASP.NET as
Jeremy Harris wrote:
Chander Ganesan wrote:
Inserts don't generate dead tuples, and AVD looks at obsolete
tuples.. As such, I wouldn't expect AVD to kick off until after you
did a mass delete...assuming that delete was sizable enough to
trigger a vacuum.
Ah, that would explain it -
On Wed, 2008-01-30 at 20:47 +0900, Jason Topaz wrote:
I don't disagree with your point that it's not robust with examples of
exactly how a particular problem can be solved. But I think there are
enough, and more importantly, I don't think problem-solving is an
important focus for a manual
On Wed, 30 Jan 2008 13:20:58 -0500
Tom Hart [EMAIL PROTECTED] wrote:
I have 4 years of mySQL experience (I know, I'm sorry)
Why is this something to apologize for? I used to use MySQL for
everything and now use PostgreSQL for the majority of my DB needs. I
certainly advocate PG now to anyone
On Wed, 2008-01-30 at 20:14 -0600, Josh Trutwin wrote:
On Wed, 30 Jan 2008 13:20:58 -0500
Tom Hart [EMAIL PROTECTED] wrote:
I have 4 years of mySQL experience (I know, I'm sorry)
Why is this something to apologize for? I used to use MySQL for
everything and now use PostgreSQL for the
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, 30 Jan 2008 20:14:15 -0600
Josh Trutwin [EMAIL PROTECTED] wrote:
On Wed, 30 Jan 2008 13:20:58 -0500
Tom Hart [EMAIL PROTECTED] wrote:
I have 4 years of mySQL experience (I know, I'm sorry)
Why is this something to apologize for?
I
In the hopes that someone has already blazed this trail ...
I'm running Postgresql (v8.1.10) on Solaris 10 (Sparc) from within a non-global
zone. I originally had the database storage in the non-global zone (e.g.
/var/local/pgsql/data on a UFS filesystem) and was getting performance of X
Tom Hart wrote:
I definitely think that the lists are one of the shining stars for
postgresql support. I've learned some good reference stuff from online
docs/google but the really tricky questions were only answered here, and
amazingly enough, quickly and with good humor. Perhaps what we
Bruce Momjian said:
Tom Hart wrote:
I definitely think that the lists are one of the shining stars for
postgresql support. I've learned some good reference stuff from online
docs/google but the really tricky questions were only answered here, and
amazingly enough, quickly and with
I suggest to explicitly invite the Russian folks too.
Oleg showed strong interest in a global certification thing.
we can contribute some material and so on if needed. it is currently
in german but it should not be a big problem.
many thanks,
hans
On Jan 30, 2008,
82 matches
Mail list logo
