[EMAIL PROTECTED] (Craig Bryden) wrote in
news:[EMAIL PROTECTED]:
Hi Sean
Thanks for that. Does psql work differently to pgAmin III's Query
program? I have tried exactly what you showed me below, and it did not
work. It seems like the script stops on first error, and the first
error is
Mike Nolan wrote:
Maybe you could return a refcursor pointing to the EXPLAIN ANALYZE of
the query inside the function.
The raw materials exist to do this: if you know which elements of a
query will be replaced by plpgsql variables, you can duplicate the
results via
PREPARE foo(...) AS ...
On May 3, 2005, at 2:50 AM, Rolf Østvik wrote:
[EMAIL PROTECTED] (Craig Bryden) wrote in
news:[EMAIL PROTECTED]:
Hi Sean
Thanks for that. Does psql work differently to pgAmin III's Query
program? I have tried exactly what you showed me below, and it did not
work. It seems like the script stops on
Hi, I made function:
CREATE FUNCTION _t(varchar,integer) RETURNS varchar AS '
DECLARE
str ALIAS FOR $1;
lang ALIAS FOR $2;
value varchar;
BEGIN
SELECT t.txt INTO value FROM sys_txt t INNER JOIN sys_txt_code c ON c.id =
t.code_id WHERE ''#''||c.code||''#'' = str AND t.lang_id = lang;
Margusja wrote:
Hi, I made function:
CREATE FUNCTION _t(varchar,integer) RETURNS varchar AS '
DECLARE
str ALIAS FOR $1;
lang ALIAS FOR $2;
value varchar;
BEGIN
SELECT t.txt INTO value FROM sys_txt t INNER JOIN sys_txt_code c ON c.id =
t.code_id WHERE ''#''||c.code||''#'' = str AND
Title: iterate over refcursor
hi,
i have some questions about
psql.
i'm trying to writea script for
deploying my database and i have some questions
:
1- is it possible to set a default schema
?
i tried : set DEFAULT
SCHEMA base1; but it didn't worked
2- is it possible to have one file
Hello
1- is it possible to set a default schema ?
i tried : set DEFAULT SCHEMA base1; but it didn't worked
SET search_path to base1;
2- is it possible to have one file with global values and to use them when
creating functions.
one example is : i have a temporary directory, and
Greg,
thanks for the suggestion. looking into other thread on the list, it
looks like setting
$dbh-{pg_server_prepare} = 0;
would solve my problem as well. With this setting will dbd::pg behave
in old-style (i.e. prepare_cached prepared and stored on dbd::pg
side), or it won't cache anything at
Hi,
Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep integration
of PostgreSQL with Apache. What I mean by deep integration is that the
PostgreSQL server logic runs inside the Apache server processes, rather
than
sorry, i badly explain my second problem (and it make me think about an other
question :) )
what i really want to say is :
i would like to declare a global variable :
DEFINE pool = 10
and use it into a function
@createMDNS.sql pool
and my file createMDNS.sql is
create or
Hi list,
I'm running postgresql 8.0.1 on FreeBSD 4.11-STABLE, the machine is
and AMD Sempron 2.2, 1GB Ram..
I use postgresql as database for dspam, an spam classification
program. This database have and moderated use, on averange 10
simultaneous conections executing relative big queries using
Supose I have this function
CREATE OR REPLACE my_func(TEXT) RETURNS text AS '
DECLARE
var_name ALIAS FOR $1;
rec RECORD;
BEGIN
SELECT * INTO rec FROM my_table WHERE my_key = 1;
-- Here is my problem
RETURN rec.var_name;
END;
' LANGUAGE plpgsql;
SELECT
On Tue, 3 May 2005, FERREIRA, William (COFRAMI) wrote:
sorry, i badly explain my second problem (and it make me think about an other
question :) )
what i really want to say is :
i would like to declare a global variable :
There is one big difference. PostgreSQL don't know global variables.
On Tue, 2005-05-03 at 07:53, Robin Boerdijk wrote:
Hi,
Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep integration
of PostgreSQL with Apache. What I mean by deep integration is that the
PostgreSQL server logic
Robin Boerdijk wrote:
Hi,
Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep integration
of PostgreSQL with Apache. What I mean by deep integration is that the
PostgreSQL server logic runs inside the Apache server
the search_path works well
thanks a lot
-Message d'origine-
De : Pavel Stehule [mailto:[EMAIL PROTECTED]
Envoyé : mardi 3 mai 2005 16:09
À : FERREIRA, William (COFRAMI)
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] some questions : psql
On Tue, 3 May 2005, FERREIRA, William
In planning a Postgresql deployment, our team discovered that we have
different understandings of how the WAL affects database reliability,
and we have not found the Postgresql manual quite explicit enough to
distinguish between the following two theories:
a) Putting the WAL on a separate
Alexandre Biancalana [EMAIL PROTECTED] writes:
Watching postgresql logs I see the following messages ocurs a lot of
times in a day:
May 3 06:58:44 e-filter postgres[250]: [21-1] LOG: server process
(PID 59608) was terminated by signal 10
You need to find out what's triggering that.
Ricardo Vaz Mannrich [EMAIL PROTECTED] writes:
Is it possible?
Not in plpgsql. I believe you could do it in any of the other PLs though.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your
On Tue, 2005-05-03 at 08:39, Alexandre Biancalana wrote:
Hi list,
I'm running postgresql 8.0.1 on FreeBSD 4.11-STABLE, the machine is
and AMD Sempron 2.2, 1GB Ram..
I use postgresql as database for dspam, an spam classification
program. This database have and moderated use, on averange
Brandon Craig Rhodes [EMAIL PROTECTED] writes:
And the disputed point:
- If the drive holding the WAL fails, then the database engine
will shut down cleanly by writing everything in RAM out to
the real database tables, and no data will be lost.
Whoever
Thomas Hallgren wrote:
Robin Boerdijk wrote:
Hi,
Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep integration
of PostgreSQL with Apache. What I mean by deep integration is that the
PostgreSQL server logic runs inside the
--- Thomas Hallgren [EMAIL PROTECTED] wrote:
Robin Boerdijk wrote:
Hi,
Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep
integration
of PostgreSQL with Apache. What I mean by deep integration is that
the
Apologies if this has been discussed before, but I was wondering
if
there have been any efforts in the past to provide a deep
integration
of PostgreSQL with Apache. What I mean by deep integration is that
the
PostgreSQL server logic runs inside the Apache server processes,
rather
Alexandre,
I saw reports (and observed the problem myself) that all sort of
different softwares suffering from signal 11 under FreeBSD (more often
seen on 5-STABLE). So far the collection is: Apache 1.3 (myself),
Mysql (recent descussion on freebsd-stable list) and now postgresql...
The hardware
oops... you were writing about signal 10 not signal 11. my bad - sorry
On 5/3/05, Vlad [EMAIL PROTECTED] wrote:
Alexandre,
I saw reports (and observed the problem myself) that all sort of
different softwares suffering from signal 11 under FreeBSD (more often
seen on 5-STABLE). So far the
The first beta of pgEdit 1.1 is now available. New features and
enhancements include:
- Error information is collected with each execution. New commands to
jump to
each error location in the file even if lines are added or removed.
- Improved integration with psql; most psql commands are now
I am newbie. Could you give a example?
Thank you.
Ricardo Vaz Mannrich [EMAIL PROTECTED] writes:
Is it possible?
Not in plpgsql. I believe you could do it in any of the other PLs though.
regards, tom lane
---(end of broadcast)---
TIP 9:
Hows does MVCC handle two concurrent tasks trying to insert the same
row? Example pseudo-code:
select row from table where...
if not found,
prepare row
insert row
else
update row
... continue processing
what happens if TASK1 inserts the row first, and continues processing
(no commit
On Tue, May 03, 2005 at 09:54:03AM -0500, Scott Marlowe wrote:
You might want to look in your signal man page on BSD and see what
signal 10 means. On solaris it's a bus error. Not a clue what it is in
FreeBSD myself though.
Signal 10 is SIGBUS (bus error) on FreeBSD 4.11. Somewhere under
Like I said before get the personal/standard version of Delphi 6,7 or
2005, it's 99.99 and you can connect to postgres with it using third
party component sets like Zeos. (2005 may not be available yet)
Zeos was ported to http://www.lazarus.freepascal.org/ (a free Delphi).
I did test the
You need to find out what's triggering that. Turning on query logging
would be a good way of investigating.
Which directives can I use to enable this ?
debug_print_parse ? debug_print_rewritten ? debug_print_plan ?
debug_pretty_print ?
Rather large, shared buffers for a machine with only 1
Wouldn't it just be cheaper to buy a second machine than do all of that?
Alex Turner
netEconomist
On 5/3/05, Robin Boerdijk [EMAIL PROTECTED] wrote:
--- Thomas Hallgren [EMAIL PROTECTED] wrote:
Robin Boerdijk wrote:
Hi,
Apologies if this has been discussed before, but I was wondering
On Tue, May 03, 2005 at 01:36:13PM -0300, Alexandre Biancalana wrote:
The system does not generate core dump file for this error.
Are you sure? Where did you look and what file name did you look
for? Unless you've changed the kern.corefile sysctl setting, the
file should be named
I may have missed this in the docs; if so, please tell me where to find the
answer there. If not, I still need to learn how to resolve this situation.
The database schema has been designed and the tables are ready to be
created. Once they exist, I want to load data into the tables in batch
On Tue, May 03, 2005 at 10:37:03AM -0600, Michael Fuhr wrote:
Signal 10 is SIGBUS (bus error) on FreeBSD 4.11. Somewhere under
$PGDATA there might be a core dump named postmaster.core
Correction: the core dump should be named postgres.core (at least
it is on my FreeBSD 4.11-STABLE system if
On Tue, 2005-05-03 at 11:36, Alexandre Biancalana wrote:
You need to find out what's triggering that. Turning on query logging
would be a good way of investigating.
Which directives can I use to enable this ?
debug_print_parse ? debug_print_rewritten ? debug_print_plan ?
On Tue, 2005-05-03 at 10:30 -0400, Brandon Craig Rhodes wrote:
theories:
a) Putting the WAL on a separate device from the database tables
not only increases efficiency, but reliability as well - because
as long as one keeps a database backup and a WAL history that
goes
I agree that it is not trivial, but is it feasible? Specifically, I'm
thinking about the following approach:
Everything is feasible but that doesn't mean it is sane to do so :). If
you were going to do that you would probably be better served looking at
something like sqllite.
Sincerely,
Joshua
If it's not possible, can I create a function that gets a RECORD and a
tablename and returns the correct value. For example:
CREATE FUNCTION my_value(TEXT, RECORD) RETURNS TEXT AS '
DECLARE
table_name ALIAS FOR $1
rec ALIAS FOR $2
BEGIN
IF (table_name = 'my_table1') THEN
On Tue, 2005-05-03 at 11:58, Rich Shepard wrote:
I may have missed this in the docs; if so, please tell me where to find the
answer there. If not, I still need to learn how to resolve this situation.
The database schema has been designed and the tables are ready to be
created. Once
On Tue, 3 May 2005, Scott Marlowe wrote:
When loading in data using the copy command, one can set the columns to be
copied, and the system will insert the serial value for you if you leave
that column out of the list.
Scott,
Aha! I thought this would be the case, but I was not sure.
Many thanks,
I have a column that I want to sort by certain values. The values are
Unit, Exterior and Common. I want all the records with Unit first,
Common second and Exterior last in the sort order. These are the only 3
possible values, is there a way to sort manually like that with the
alphanumeric values?
Hi
How in postgres can I do date/time subtraction or addition.
e.g. If I want to get today's date - 30 days? or current_timestamp - 1 hour?
Thanks
Craig
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
It's easy. You have to know that INTERVAL data type exist, so:
SELECT current_date - '30 days'::interval
SELECT current_timestamp - '1 hour'::interval
2005/5/3, Craig Bryden [EMAIL PROTECTED]:
HiHow in postgres can I do date/time subtraction or addition.e.g. If I want to get today's date - 30
On 5/3/05, Scott Marlowe [EMAIL PROTECTED] wrote:
On Tue, 2005-05-03 at 11:36, Alexandre Biancalana wrote:
You need to find out what's triggering that. Turning on query logging
would be a good way of investigating.
Which directives can I use to enable this ?
debug_print_parse ?
On Tue, 2005-05-03 at 12:25, Alexandre Biancalana wrote:
On 5/3/05, Scott Marlowe [EMAIL PROTECTED] wrote:
On Tue, 2005-05-03 at 11:36, Alexandre Biancalana wrote:
You need to find out what's triggering that. Turning on query logging
would be a good way of investigating.
Which
You can order by conditions, lets say column='Unit'. The evaluation of
a conditions will give you 't' or 'f', and alfabetically 'f'
't'... you should use DESC to get the matches first. So, it would be
more or less like this:
ORDER BY
column='Unit' DESC,
column='Exterior' DESC,
column='Common'
On Tue, 2005-05-03 at 12:32, Craig Bryden wrote:
Hi
How in postgres can I do date/time subtraction or addition.
e.g. If I want to get today's date - 30 days? or current_timestamp - 1 hour?
select now()-interval'1 hour'
and so on.
---(end of
unsubscribe -farewell general [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Tue, 2005-05-03 at 12:29, Robert Fitzpatrick wrote:
I have a column that I want to sort by certain values. The values are
Unit, Exterior and Common. I want all the records with Unit first,
Common second and Exterior last in the sort order. These are the only 3
possible values, is there a
On Tue, 2005-05-03 at 19:32 +0200, Craig Bryden wrote:
How in postgres can I do date/time subtraction or addition.
e.g. If I want to get today's date - 30 days? or current_timestamp - 1 hour?
easier than you think
select current_timestamp - interval '1 hour';
select current_date -interval
On Tue, 2005-05-03 at 13:29 -0400, Robert Fitzpatrick wrote:
I have a column that I want to sort by certain values. The values are
Unit, Exterior and Common. I want all the records with Unit first,
Common second and Exterior last in the sort order. These are the only 3
possible values, is
On Tue, May 03, 2005 at 12:28:05PM -0400, Allen wrote:
Hows does MVCC handle two concurrent tasks trying to insert the same
row? Example pseudo-code:
select row from table where...
if not found,
prepare row
insert row
else
update row
... continue processing
what happens
All,
Thank you for the confirmation and reply.
DC
--
--- Devrim GUNDUZ [EMAIL PROTECTED] wrote:
Anyway, I think it will be better to fix it
compeletely in 7.3.10.
Apologies everyone :(
It will probably work, but I haven't compared the
7.3 and 7.4
initscripts in
Thank you for the detailed explanation Scott, they are very handy !!
I reduced the shared_buffers to 32768, but the problem still occurs.
Any other idea ??
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister
On Tue, 2005-05-03 at 15:04, Alexandre Biancalana wrote:
Thank you for the detailed explanation Scott, they are very handy !!
I reduced the shared_buffers to 32768, but the problem still occurs.
Any other idea ??
Yeah, I had a sneaking suspicion that shared_buffers wasn't causing the
Ohhh god :(
The FreeBSD is the last STABLE version. I can try to change some
hardware, I already changed memory, what can I try now ? the processor
? motherboard ??
On 5/3/05, Scott Marlowe [EMAIL PROTECTED] wrote:
On Tue, 2005-05-03 at 15:04, Alexandre Biancalana wrote:
Thank you for
Robin Boerdijk wrote:
Why would
this web/database server be limited to using only one connection?
No it wouldn't. I misunderstood your question. It's one process/one
connection. If you don't use Apache as a multi-threaded server, then
your question makes more sense. Still, it's a lot of work to
http://www.suite101.com/article.cfm/oracle/115560
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Can dollar quoting be used with plpythonu (like with
plperl -
http://www.postgresql.org/docs/8.0/interactive/plperl.html#PLPERL-FUNCS)?
When trying to create a function I get this error:
'syntax error at or near $'
If I use single quotes, then escape all quotes and (a
lot of) backslashes in the
It's nice, but...it couldn't possibly be more subjective, and his ranking
of documentation as 5 out of 10 is funny. He apparently did not even find
these many hosted listservs (only Usenet). shrug
I hope he enjoys his ongoing reviews; I suppose it can't hurt?
-Original Message-
From:
http://www.cs.cmu.edu/~harchol/Papers/actual-icde-submission.pdf
---(end of broadcast)---
TIP 8: explain analyze is your friend
I asked this question several weeks ago, but nobody proposed a solution, so
I am repeating the same question again...
I have an MS Access front-end for a database on PostgreSQL.
I could use pass-through queries as record sources for reports and it works
fine...
Unfortunately, MS Access doesn't
Nevermind, I see dollar-quoting was added in 8.0.
--- CSN [EMAIL PROTECTED] wrote:
Can dollar quoting be used with plpythonu (like with
plperl -
http://www.postgresql.org/docs/8.0/interactive/plperl.html#PLPERL-FUNCS)?
When trying to create a function I get this error:
'syntax error at
On Tue, May 03, 2005 at 02:46:04PM -0700, CSN wrote:
Can dollar quoting be used with plpythonu (like with plperl -
Yes, if you're using PostgreSQL 8.0 or later. Dollar quotes have
nothing to do with the function's language -- they're just another
way to quote a string.
I am trying to come up with a method to have a default value for a
column based on a function and other columns. I'm hoping ( well not too
much ) that what I figure out here will apply to MS SQL Server as I am
stuck using it unless I can prove there ain't no way it's going to
happen. ( Now
On Tue, 2005-05-03 at 17:31, Roderick A. Anderson wrote:
I am trying to come up with a method to have a default value for a
column based on a function and other columns. I'm hoping ( well not too
much ) that what I figure out here will apply to MS SQL Server as I am
stuck using it unless I
For those who didn't see it on slashdot:
http://www.acmqueue.org/modules.php?name=Contentpa=showpagepid=293
What's interesting is that PostgreSQL is already working on some of
these things. Of note, there's a patch to allow sequential scans to
'piggyback' on top of other sequential scans. See the
w/o reading the URL docs, it sounds suspiciously like tagged command queueing
for sequential scans.
pause for comedic effect
I wonder what the best way to spend $7K for performance improvement might be?
;-)
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I think the closest you are going to get is Apache::DBI however sense
PostgreSQL is processed based you are going to get a new connection
for every connection to Apache.
On any reasonably busy site that can spell doom.
Actually, this is a
I'm probably asking a FAQ, but a few google searches didn't seem
to point me in the right place.
Is there a simple way with PostgreSQL to assign relative ranks to the
result of a query ORDER BY? That is, I want to either have a view
that cheaply assigns the ranks, or be able to update a column
On May 3, 2005, at 8:30 PM, Randal L. Schwartz wrote:
Is there a simple way with PostgreSQL to assign relative ranks to the
result of a query ORDER BY?
What do you mean by ranks?
---(end of broadcast)---
TIP 9: the planner will ignore your desire
Matthew == Matthew Terenzio [EMAIL PROTECTED] writes:
Matthew On May 3, 2005, at 8:30 PM, Randal L. Schwartz wrote:
Is there a simple way with PostgreSQL to assign relative ranks to the
result of a query ORDER BY?
Matthew What do you mean by ranks?
If I order a query by ascending age, the
Randal L. Schwartz wrote:
I'm probably asking a FAQ, but a few google searches didn't seem
to point me in the right place.
Is there a simple way with PostgreSQL to assign relative ranks to the
result of a query ORDER BY? That is, I want to either have a view
that cheaply assigns the ranks, or be
How about something like:
CREATE TABLE testrank (
id int,
value varchar
);
insert into testrank values(17,'way');
insert into testrank values(27,'foo');
insert into testrank values(278,'bar');
insert into testrank values(1,'abd');
insert into testrank values(2,'def');
CREATE OR REPLACE FUNCTION
If I order a query by ascending age, the youngest person gets
rank 1, the second youngest gets rank 2, the third youngest gets rank 3,
and if the fourth and fifth tie, they both get 4, and the next one gets 6.
You know, rank? :)
You could use a plPerl function.
Sincerely,
Joshua D. Drake
If I order a query by ascending age, the youngest person gets
rank 1, the second youngest gets rank 2, the third youngest gets rank 3,
and if the fourth and fifth tie, they both get 4, and the next one gets 6.
You know, rank? :)
You could use a plPerl function.
To do it with ties,
Dann Corbit [EMAIL PROTECTED] writes:
http://www.suite101.com/article.cfm/oracle/115560
Well, he does say that this is only a comparison of the install
experience ... but I read that as the win32 installer guys get a
gold star. Congrats guys!
regards, tom lane
Zlatko Matic wrote:
I asked this question several weeks ago, but nobody proposed a solution,
so I am repeating the same question again...
I have an MS Access front-end for a database on PostgreSQL.
I could use pass-through queries as record sources for reports and it
works fine...
Unfortunately,
Select current_timestamp - '30 day'::interval
Select current_timestamp - '1 hour'::interval
Thanks
Dinesh Pandey
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Craig Bryden
Sent: Tuesday, May 03, 2005 11:02 PM
To: pgsql
Subject: [GENERAL] Date
Hello
everyone,
Anyone can
help me find out RPM packs for Postgre 8.0 , i need it for Linux
"i586"
I just find RPMs for
version i686 :(
Anybody more luck
than me?
thanks in
advance
Atenciosamente,
Lipy Reis
Analista de Suporte
===
quando é hora
Zlatko Matic [EMAIL PROTECTED] writes:
In regular tables, I use bigserial field, but how can I create calculated
bigserial column in a view ?
You would have to create a sequence and reference it with
nextval('sequencename') in your view. But I doubt very much that it will do
anything useful.
83 matches
Mail list logo