I am using Postgresql 8.3
I have two databases:
db1
db2
db1 holds a table tab1.
Is it possible to get the value of the above tab1 in db2 database?
Regards
Hello,
Pavel Stehule claviota:
...
you cannot get crosstab via SELECT statement. There is workaround
http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
All right, I've just tried it: it works just fine in my case! Thanks a lot!
Except a few things, but I am not (yet)
2010/1/27 Pierre Chevalier pierre.chevalier1...@free.fr:
Hello,
Pavel Stehule claviota:
...
you cannot get crosstab via SELECT statement. There is workaround
http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
All right, I've just tried it: it works just fine in my
Thanks Craig, this one worked!
Balazs
-Original Message-
From: Craig Ringer [mailto:cr...@postnewspapers.com.au]
Sent: Wednesday, January 27, 2010 12:34 AM
To: Keresztury Balázs
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] create role in a pl/pgsql trigger
On 27/01/2010 1:09
2010/1/27 Pierre Chevalier pierre.chevalier1...@free.fr:
Pavel Stehule claviota:
...
But what I would like to do is to redirect the output of the function
(that
is, the 'result' cursor) to a view, which will be used in other places. I
thought something like FETCH INTO would do the trick,
I have a stored procedure execute the following code :
INSERT INTO unit(fk_lot_id, cycle)
VALUES(_lotID, _cycle) RETURNING * INTO _unit;
raise notice 'AFTER INSERT INTO UNIT, _unit.unit_id = %', _unit.unit_id ;
unit_id column, is an auto generated primary key. I will always get a
2010/1/27 Pierre Chevalier pierre.chevalier1...@free.fr:
Pavel Stehule claviota:
...
Actually, if the small application was reading cursor, and transforming
it
to a VIEW, this would solve both problems at once:
something like:
CREATE VIEW crosstabbed_thing AS
(cursor_to_dataset(SELECT
On Tue, Jan 26, 2010 at 05:12:51PM -0500, Greg Smith wrote:
Hashimoto Yuya wrote:
Judging from the result, I could see that stats collector process
caused this unusually high CPU utilization rate.
I found similar problem at
Hello
I add sprintf function. Now I think, we can add new contrib module
(string functions) with both function - format and sprintf. These
functions are relative different, so they can exists separately.
Format is simpler and faster. Sprintf is more powerful but slower.
postgres=# select
Hello,
Let's say TableA has toastable column, the contents of this column is
stored in let's say pg_toast_1234.
Is there a query to find which records (chunk_id, chunk_seq) in
pg_toast_1234 store data for specific record in TableA (i.e. with PK
column value eq. '567')?
Igor Neyman
2010/1/27 Pavel Stehule pavel.steh...@gmail.com:
Hello
I add sprintf function. Now I think, we can add new contrib module
(string functions) with both function - format and sprintf. These
functions are relative different, so they can exists separately.
Format is simpler and faster. Sprintf
2010/1/27 Vincenzo Romano vincenzo.rom...@notorand.it:
2010/1/27 Pavel Stehule pavel.steh...@gmail.com:
Hello
I add sprintf function. Now I think, we can add new contrib module
(string functions) with both function - format and sprintf. These
functions are relative different, so they can
Hello,
I have a function with three parameters that would populate a table in
one schema from another table of the same name in another schema. The
tables are dynamically selected at execution time.
CREATE OR REPLACE FUNCTION schema_1.getAllSnapShot(user_id text,
begin_dt date, end_dt
On 27/01/2010 15:40, Aycock, Jeff R. wrote:
BEGIN
FOR r IN SELECT * FROM schema_1.snapshot_table
LOOP
DECLARE whoami text := r;
I could be wrong, but I don't think that the DECLARE inside the loop is
correct. I think you have to declare whoami with the rest of
Vincenzo Romano vincenzo.rom...@notorand.it writes:
But why still on separate schema?
I'd rather put them all in the public one, so you don't need the pst.
anymore.
Just like (most of) all other contrib mudules ...
If this were to get committed, it would definitely get made to look just
2010/1/27 Pavel Stehule pavel.steh...@gmail.com:
2010/1/27 Vincenzo Romano vincenzo.rom...@notorand.it:
But why still on separate schema?
I'd rather put them all in the public one, so you don't need the pst.
anymore.
Just like (most of) all other contrib mudules ...
if you like, you can
2010/1/27 Tom Lane t...@sss.pgh.pa.us:
Vincenzo Romano vincenzo.rom...@notorand.it writes:
But why still on separate schema?
I'd rather put them all in the public one, so you don't need the pst.
anymore.
Just like (most of) all other contrib mudules ...
If this were to get committed, it
2010/1/27 Tom Lane t...@sss.pgh.pa.us:
Vincenzo Romano vincenzo.rom...@notorand.it writes:
But why still on separate schema?
I'd rather put them all in the public one, so you don't need the pst.
anymore.
Just like (most of) all other contrib modules ...
If this were to get committed, it
Raymond,
I tried your suggestion but the result is the same when whoami is
declared at the top of the function and assigned inside the loop.
Thanks for the suggestion anyway.
-Original Message-
From: Raymond O'Donnell [mailto:r...@iol.ie]
Sent: Wednesday, January 27, 2010 11:00 AM
To:
ERROR: syntax error at or near (
LINE 1: SELECT *, $1, now() INTO schema_1.(table_A) FROM schema_2.(table_A)
where created_dt between $2 and $3;
schema_1.(table_A) is nonsense. have to be schema_1.table_A
regards
Pavel Stehule
--
Sent via pgsql-general mailing list
Pavel,
Per your suggestion I modified one line below BEGIN to look like this:
EXECUTE 'SELECT *, $1, now() INTO schema_1.'||whoami||' FROM
schema_2.'||whoami||' where created_dt between $2 and $3;'
However, it is still giving me the same syntax error as before. I must be
missing something
On 01/27/2010 08:27 AM, Aycock, Jeff R. wrote:
Pavel,
Per your suggestion I modified one line below BEGIN to look like this:
EXECUTE 'SELECT *, $1, now() INTO schema_1.'||whoami||' FROM
schema_2.'||whoami||' where created_dt between $2 and $3;'
However, it is still giving me the same syntax
Aycock, Jeff R. jeff.r.ayc...@saic.com wrote:
Pavel,
Per your suggestion I modified one line below BEGIN to look like this:
EXECUTE 'SELECT *, $1, now() INTO schema_1.'||whoami||' FROM
schema_2.'||whoami||' where created_dt between $2 and $3;'
Yan Cheng Cheok ycch...@yahoo.com writes:
I have a stored procedure execute the following code :
INSERT INTO unit(fk_lot_id, cycle)
VALUES(_lotID, _cycle) RETURNING * INTO _unit;
raise notice 'AFTER INSERT INTO UNIT, _unit.unit_id = %', _unit.unit_id ;
unit_id column, is an
Adrian,
I tried that as well and got the same error result.
Regards,
Jeff Aycock
-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com]
Sent: Wednesday, January 27, 2010 11:33 AM
To: Aycock, Jeff R.
Cc: Pavel Stehule; pgsql-general@postgresql.org
Subject: Re:
On Wed, Jan 27, 2010 at 02:52:58PM +0600, AI Rumman wrote:
I am using Postgresql 8.3
I have two databases:
db1
db2
db1 holds a table tab1.
Is it possible to get the value of the above tab1 in db2 database?
Regards
You'll need something like the
On 01/27/2010 08:41 AM, Aycock, Jeff R. wrote:
Adrian,
I tried that as well and got the same error result.
Regards,
Jeff Aycock
I went back to the original function and assuming no cut/paste errors
there is a ';' missing after the last END.
END;
RETURN NEXT r;
Thanks for the catch.
However, this did not fix the syntax error.
-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com]
Sent: Wednesday, January 27, 2010 11:47 AM
To: Aycock, Jeff R.
Cc: Pavel Stehule; pgsql-general@postgresql.org
Subject: Re: [GENERAL] syntax error
On 01/27/2010 08:50 AM, Aycock, Jeff R. wrote:
Thanks for the catch.
However, this did not fix the syntax error.
You are sure the function is being replaced with versions that have the
changes? In other words does \df+ show the changes?
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent
On 01/27/2010 08:50 AM, Aycock, Jeff R. wrote:
Thanks for the catch.
However, this did not fix the syntax error.
Looking back at this thread I second Andreas's suggestion. It seems the
syntax is right but the names are wrong. What is the result when you do
SELECT * FROM
On Wed, Jan 27, 2010 at 10:40:17AM -0500, Aycock, Jeff R. wrote:
EXECUTE 'SELECT *, $1, now() INTO schema_1.'||whoami||' FROM
schema_2.'||whoami||' where created_dt between $2 and $3;'
You'll also need to expand those other parameters. The code is executed
in an independent scope and hence PG
Osvaldo,
That did the trick! Like you said, it had to do with the composite type. I
added the column name to the row variable assignment and it works now.
Thanks for the tip and thanks to everybody else for your assistance.
Have a great day.
Regards,
Jeff Aycock
-Original
Many people have been disappointed by the lack of a PL/R Windows binary
distribution since PostgreSQL 8.3 came out. Unfortunately the switch
from MinGW to MSVC as the Windows build system in Postgres 8.3
effectively rendered Windows an unsupported platform for PL/R.
I *finally* have made
Hi all. I have a problem with two tables of same structure:
'orders_stat_pre' and 'orders_stat_pre_new'.
store=# \d orders_stat_pre
Column | Type | Modifiers
++---
id | integer| not
Pavel Stehule claviota:
...
Actually, if the small application was reading cursor, and transforming it
to a VIEW, this would solve both problems at once:
something like:
CREATE VIEW crosstabbed_thing AS
(cursor_to_dataset(SELECT do_cross_cursor(...)));
no it isn't possible. VIEW have to
Pavel Stehule claviota:
...
But what I would like to do is to redirect the output of the function (that
is, the 'result' cursor) to a view, which will be used in other places. I
thought something like FETCH INTO would do the trick, but it doesn't.
Also, I need, at some point, to export the
On 27 Jan 2010, at 2:00, Yan Cheng Cheok wrote:
However, whenever I insert row into measurement table,
I realize its primary key value is going from 2, 4, 6, 8,
10...
May I know how can I prevent this?
Apparently nextval on that sequence gets called multiple
times in your queries.
On 27 Jan 2010, at 4:22, Yan Cheng Cheok wrote:
Hello all,
I solve my problem using the following. It seems that when inherit from
parent table, the parent table's constraint is not being carried over to
child table.
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS
Hi!
I'm happy to facilitate this and get the details in for our
application. Seems like we have lots of things that we could get
students involved with, and of course, we tend to get interesting
projects pitched to us that we haven't thought of before.
I've attended the Mentor Summit after GSoC
On 1/27/2010 7:32 AM, J Scanf wrote:
Hi all. I have a problem with two tables of same structure:
'orders_stat_pre' and 'orders_stat_pre_new'.
store=# \d orders_stat_pre
Column | Type | Modifiers
++---
id
I have deployed PostgresSQL 8.4.1 on a Fedora 9 c1.xlarge (8x1 cores) instance
in the Amazon E2 Cloud. When I run pgbench in read-only mode (-S) on a small
database, I am unable to peg the CPUs no matter how many clients I throw at it.
In fact, the CPU utilization never drops below 60% idle. I
On Wed, Jan 27, 2010 at 3:59 PM, Mike Bresnahan
mike.bresna...@bestbuy.comwrote:
I have deployed PostgresSQL 8.4.1 on a Fedora 9 c1.xlarge (8x1 cores)
instance
in the Amazon E2 Cloud. When I run pgbench in read-only mode (-S) on a
small
database, I am unable to peg the CPUs no matter how
I'm not getting something about the best way to set up a server using
PostgreSQL as a backend for a busy web server running drupal.
The postgresql performance folks
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
say that in a server with more that 1GB of ram
a reasonable
Jeff Ross jr...@wykids.org writes:
Running a simple select only pgbench test against it will fail with an out of
memory error as it tries to vacuum --analyze the newly created database with
750 tuples.
Better look at the ulimit values the postmaster is started with;
you shouldn't be
Jim Mlodgenski jimmy76 at gmail.com writes:
I have seen behavior like this in the past on EC2. I believe your bottleneck
may be pulling the data out of cache. I benchmarked this a while back and found
that memory speeds are not much faster than disk speeds on EC2. I am not sure if
that is true of
Tom Lane wrote:
Jeff Ross jr...@wykids.org writes:
Running a simple select only pgbench test against it will fail with an out of
memory error as it tries to vacuum --analyze the newly created database with
750 tuples.
Better look at the ulimit values the postmaster is started with;
you
Mike Bresnahan wrote:
top - 15:55:05 up 1:33, 2 users, load average: 2.44, 0.98, 0.44
Tasks: 123 total, 11 running, 112 sleeping, 0 stopped, 0 zombie
Cpu(s): 18.9%us, 8.8%sy, 0.0%ni, 70.6%id, 0.0%wa, 0.0%hi, 1.7%si, 0.0%st
Mem: 7348132k total, 1886912k used, 5461220k free,
I have seen behavior like this in the past on EC2. I believe your
bottleneck may be pulling the data out of cache. I benchmarked this a
while back and found that memory speeds are not much faster than disk
speeds on EC2. I am not sure if that is true of Xen in general or if
its just limited
John R Pierce pierce at hogranch.com writes:
more likely, he's disk IO bound, but hard to say as that iostat output
only showed a couple 2 second slices of work. the first output, which
shows average since system startup, seems to show the system has had
relatively high average wait times
Could you try this again with top -c, which will label these
postmaster processes usefully, and include the pgbench client itself in
what you post? It's hard to sort out what's going on in these
situations without that style of breakdown.
I had run pgbench on a separate instance last
Greg Smith greg at 2ndquadrant.com writes:
Could you try this again with top -c, which will label these
postmaster processes usefully, and include the pgbench client itself in
what you post? It's hard to sort out what's going on in these
situations without that style of breakdown.
As a
Jeff Ross jr...@wykids.org writes:
Tom Lane wrote:
Better look at the ulimit values the postmaster is started with;
OpenBSD makes a _postgresql user on install and it is in the daemon class
with
the following values:
daemon:\
:ignorenologin:\
:datasize=infinity:\
Hello there.
I read http://www.postgresql.org/docs/current/static/sql-altertable.html
and find it interesting that
Adding a column with a non-null default or changing the type of an
existing column will require the entire table to be rewritten. This
might take a significant amount of time for a
Thanks and Regards
Yan Cheng CHEOK
--- On Thu, 1/28/10, Tom Lane t...@sss.pgh.pa.us wrote:
From: Tom Lane t...@sss.pgh.pa.us
Subject: Re: [GENERAL] Problem after installing triggering function
To: Yan Cheng Cheok ycch...@yahoo.com
Cc: pgsql-general@postgresql.org
Date: Thursday, January
Thanks and Regards
Yan Cheng CHEOK
--- On Thu, 1/28/10, Alban Hertroys dal...@solfertje.student.utwente.nl wrote:
From: Alban Hertroys dal...@solfertje.student.utwente.nl
Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table
Partition
To: Yan Cheng Cheok
Sorry. Some correction.
Change
But... I am not implementing table partition
to
But... I am *now* implementing table partition
Thanks and Regards
Yan Cheng CHEOK
--- On Thu, 1/28/10, Yan Cheng Cheok ycch...@yahoo.com wrote:
From: Yan Cheng Cheok ycch...@yahoo.com
Subject: Re: [GENERAL]
On Wed, Jan 27, 2010 at 6:37 PM, Mike Bresnahan
mike.bresna...@bestbuy.comwrote:
Greg Smith greg at 2ndquadrant.com writes:
Could you try this again with top -c, which will label these
postmaster processes usefully, and include the pgbench client itself in
what you post? It's hard to sort
On Wed, Jan 27, 2010 at 5:45 PM, A B gentosa...@gmail.com wrote:
Hello there.
I read http://www.postgresql.org/docs/current/static/sql-altertable.html
and find it interesting that
Adding a column with a non-null default or changing the type of an
existing column will require the entire
On Wed, Jan 27, 2010 at 2:14 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
hmm ...it cannot work :(. You cannot forward FETCH ALL statement on
server side - without programming in C
in this case you need small application for reading cursor and
transformation to CVS
If I'm understanding
I am getting the error:
LINE 1: select dblink_connect('dbname=postgres');
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
i have 2 functions, naming a and b, both outputing a resultset(cursor)
and a integer.
a calls b
a:
CREATE OR REPLACE FUNCTION public.t_outer (out o_rs
pg_catalog.refcursor, out o_i integer) RETURNS record AS
...
select t_inner(o_rs, o_i);
...
b:
CREATE OR REPLACE FUNCTION public.t_inner (out
On Wed, Jan 27, 2010 at 4:42 PM, Tom Lane t...@sss.pgh.pa.us wrote:
related to maximum per-process data space. I don't know BSD very well
so I can't say if datasize is the only such value for BSD, but it'd be
worth checking. (Hmm, on OS X which is at least partly BSDish, I see
-m and -v in
62 matches
Mail list logo
