Hi,
I'd writing a query against a function (pg_proc) that contains 2
fields of an array type. Ideally, I'd like to select 1 row from the
table, but return a query row for each item in the array.
For example, if one row contains the array {a, b, c}
I'd like the query to return 3 rows, one for
- Original Message -
From: Postgres User postgres.develo...@gmail.com
To: pgsql-general pgsql-general@postgresql.org
Sent: Friday, May 29, 2009 12:21:11 AM GMT -08:00 Tijuana / Baja California
Subject: [GENERAL] Converting each item in array to a query result row
Hi,
I'd writing
Postgres User wrote:
Hi,
I'd writing a query against a function (pg_proc) that contains 2
fields of an array type. Ideally, I'd like to select 1 row from the
table, but return a query row for each item in the array.
For example, if one row contains the array {a, b, c}
I'd like the query to
I needed to do this just the other day, here's what I did:
create or replace function explode(_a anyarray) returns setof
anyelement as $$
begin
for i in array_lower(_a,1) .. array_upper(_a,1) loop
return next _a[i];
end loop;
return;
end;
$$ language
On 29/05/2009 00:14, Guy Rouillier wrote:
You may remove yourself. At the bottom of *every* message sent from the
mail list server are these instructions:
Actually, for some reason, the instructions don't seem to have come
through on the OP's emailI was poised to say the same as you until
On Fri, May 29, 2009 at 3:37 AM, Scott Bailey arta...@comcast.net wrote:
Did you read the article I sent you earlier?
Well, the difference here is that this way db doesn't really check
anything :) you just choose path of execution, that you created prior.
That's cheating :p
So yes, I read that
Raymond O'Donnell wrote:
On 29/05/2009 00:14, Guy Rouillier wrote:
You may remove yourself. At the bottom of *every* message sent from the
mail list server are these instructions:
Actually, for some reason, the instructions don't seem to have come
through on the OP's emailI was poised to
why complicate so much ? this is a single sql query. It really shocks
me, how people easily lean on plpgsql and for/loops - which are times
slower than simple sql query:
CREATE FUNCTION unnest(anyarray) RETURNS SETOF anyelement AS
$_$
SELECT ($1)[i] FROM
Alvaro Herrera ha scritto:
Nico Sabbi wrote:
Hi,
i can't believe my eyes. Why on earth I can't drop a user without
previously revoking his privileges?
This is really _crazy_ in my opinion.
I'm not speaking of object ownership, but of GRANTs.
As Tom says, it's a known limitation.
Raymond O'Donnell wrote:
On 29/05/2009 00:14, Guy Rouillier wrote:
You may remove yourself. At the bottom of *every* message sent from the
mail list server are these instructions:
Actually, for some reason, the instructions don't seem to have come
through on the OP's emailI was poised
Always test your performance assumptions. The plpgsql function is
faster than the sql function, a lot faster on smaller arrays.
unnest - 10 element array - 100,000 times: 6701.746 ms
unnest - 100 element array - 100,000 times: 11847.933 ms
unnest - 1000 element array - 100,000 times: 59472.691
2009/5/29 Adam Ruth adamr...@mac.com:
Always test your performance assumptions. The plpgsql function is faster
than the sql function, a lot faster on smaller arrays.
unnest - 10 element array - 100,000 times: 6701.746 ms
unnest - 100 element array - 100,000 times: 11847.933 ms
unnest - 1000
I'm trying to build a customized pg_standby.c and I can not locate
pg_usleep(). Can anyone point me to the source file that contains this
library? (or even better,the associated Red Hat rpm?)
--
Until later, Geoffrey
Those who would give up essential Liberty, to purchase a little
temporary
2009/5/28 Kris Jurka bo...@ejurka.com:
On Wed, 27 May 2009, Emanuel Calvo Franco wrote:
Hi community,
I'm trying to compile pl/java sources for 8.4 beta1 (for a test) but
it gives me 20 errors at the end:
To build against 8.4 you need pljava from CVS. Also pljava can only be
built with
On Fri, May 29, 2009 at 1:19 AM, Kris Jurka bo...@ejurka.com wrote:
To build against 8.4 you need pljava from CVS. Also pljava can only be
built with the 1.4 or 1.5 JDK, not with the 1.6 version you are using.
is it a lot of work to make it 1.6 friendly ?
can I help?
--
GJ
--
Sent via
another question, what about tmdb ? it requires java6, so I assumed
that jdbc is 1.6 friendly odd.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Geoffrey wrote:
I'm trying to build a customized pg_standby.c and I can not locate
pg_usleep(). Can anyone point me to the source file that contains this
library? (or even better,the associated Red Hat rpm?)
port/pgsleep.c
If you're a perl guy might be worth getting a copy of ack too.
--
Richard Huxton wrote:
Geoffrey wrote:
I'm trying to build a customized pg_standby.c and I can not locate
pg_usleep(). Can anyone point me to the source file that contains
this library? (or even better,the associated Red Hat rpm?)
port/pgsleep.c
If you're a perl guy might be worth getting a
For some reason this doesn't give me satisfaction that it's written
optimally, but I haven't found another way.
SELECT round(CAST ((EXTRACT(EPOCH FROM clockout)
-EXTRACT(EPOCH FROM clockin))/3600 AS NUMERIC),2) AS hours
FROM timeclock;
The clockin and clockout columns are of
Geoffrey wrote:
Richard Huxton wrote:
Geoffrey wrote:
I'm trying to build a customized pg_standby.c and I can not locate
pg_usleep(). Can anyone point me to the source file that contains
this library? (or even better,the associated Red Hat rpm?)
port/pgsleep.c
If you're a perl guy might
Hi
I use a .net ODBC connection and the connection to Pgsql db is though a
firewall that only allow request and a answer to the response.
I some times get Error from the client
The connection has been disabled. --- System.Data.Odbc.OdbcException: ERROR
[08S01] No response from the backend;
I
On Fri, 29 May 2009 08:13:32 -0500 (CDT)
Brandon Metcalf bran...@geronimoalloys.com wrote:
For some reason this doesn't give me satisfaction that it's written
optimally, but I haven't found another way.
SELECT round(CAST ((EXTRACT(EPOCH FROM clockout)
-EXTRACT(EPOCH FROM
Here are 3 $POSGRES_HOME/data/postgresql.conf parameters which may help
tcp_keepalives_idle (integer)On systems that support the TCP_KEEPIDLE
socket option, specifies the
number of seconds between sending keepalives on an otherwise idle
connection. A value of 0 uses the
2009/5/29 Grzegorz Jaśkiewicz gryz...@gmail.com:
2009/5/29 Adam Ruth adamr...@mac.com:
Always test your performance assumptions. The plpgsql function is faster
than the sql function, a lot faster on smaller arrays.
unnest - 10 element array - 100,000 times: 6701.746 ms
unnest - 100 element
Adam Ruth adamr...@mac.com writes:
I needed to do this just the other day, here's what I did:
create or replace function explode(_a anyarray) returns setof
anyelement as $$
You'd be well advised to call this unnest(), because that's what the
equivalent built-in function in 8.4 is called ;-)
Nico Sabbi wrote:
Alvaro Herrera ha scritto:
I'm not speaking of object ownership, but of GRANTs.
As Tom says, it's a known limitation. Did you try REASSIGN OWNED and/or DROP
OWNED?
No, I didn't because the tables weren't owned by the user I wanted to
drop, but by another one.
DROP
Richard Huxton wrote:
Geoffrey wrote:
Richard Huxton wrote:
Geoffrey wrote:
I'm trying to build a customized pg_standby.c and I can not locate
pg_usleep(). Can anyone point me to the source file that contains
this library? (or even better,the associated Red Hat rpm?)
port/pgsleep.c
If
CREATE OR REPLACE FUNCTION explode(_a anyarray) returns setof anyelement AS
$_$
BEGIN
RAISE NOTICE 'poof... just kidding... kabooom';
RETURN unnest(_a);
END;
$_$ LANGUAGE 'plpgsql'
;]
seriously, the 8.4 version is written in C, so I will be really
surprised if it under performs plpgsql one
Brandon Metcalf bran...@geronimoalloys.com writes:
For some reason this doesn't give me satisfaction that it's written
optimally, but I haven't found another way.
SELECT round(CAST ((EXTRACT(EPOCH FROM clockout)
-EXTRACT(EPOCH FROM clockin))/3600 AS NUMERIC),2) AS hours
A. Kretschmer wrote:
In response to Igor Katson :
I want to DROP CASCADE a table, but I am afraid that amoung numerous
recursive dependencies there will be smth, that I don't want to drop.
Is there a way to watch all dependencies recursively without doing a drop?
You can walk through
On Fri, May 29, 2009 at 10:18 AM, Y W luckyas...@gmail.com wrote:
Are you referring to connection settings ? Coz if u do, the closet thing I
can find for the tcp_keepalive_timeout u're mentioning are the
tcp_keepalives_idle, tcp_keepalives_interval, and the tcp_keepalives_count.
And apart from
Adam Ruth adamr...@mac.com writes:
Always test your performance assumptions. The plpgsql function is
faster than the sql function, a lot faster on smaller arrays.
And, of course, it also pays to be precise about what you're testing
and on what. Set-returning SQL functions got a lot faster in
On Fri, May 29, 2009 at 10:55 AM, Y W luckyas...@gmail.com wrote:
But according to the documentation, are they ignored if postgres was hosted
on a Unix/linux system?
They work fine on unix, as long as the user has the option to set
their own timeouts. If not then set them for the OS (for linux
correct. Also, if the client is still alive they have no effect either.
On Fri, May 29, 2009 at 11:01 AM, Y W luckyas...@gmail.com wrote:
I'm sorry that was my bad, they're only ignored when uing Unix-domain
sockets to connect instead of TCP/IP.
On Fri, May 29, 2009 at 12:55 PM, Y W
On Fri, May 29, 2009 at 09:48:42AM -0300, Emanuel Calvo Franco wrote:
2009/5/28 Kris Jurka bo...@ejurka.com:
On Wed, 27 May 2009, Emanuel Calvo Franco wrote:
Hi community,
I'm trying to compile pl/java sources for 8.4 beta1 (for a test)
but it gives me 20 errors at the end:
To
2009/5/29 David Fetter da...@fetter.org:
On Fri, May 29, 2009 at 09:48:42AM -0300, Emanuel Calvo Franco wrote:
2009/5/28 Kris Jurka bo...@ejurka.com:
On Wed, 27 May 2009, Emanuel Calvo Franco wrote:
Hi community,
I'm trying to compile pl/java sources for 8.4 beta1 (for a test)
Grzegorz Jaśkiewicz wrote:
On Fri, May 29, 2009 at 3:37 AM, Scott Bailey arta...@comcast.net wrote:
Did you read the article I sent you earlier?
Well, the difference here is that this way db doesn't really check
anything :) you just choose path of execution, that you created prior.
That's
David Fetter wrote:
If you have access to a compiler but not CVS or git, you can get one
of the daily tarballs. Are you *sure* you can't use CVS or git,
though?
The problem is pljava, not postgresql. pljava doesn't have a daily
tarball or a git repo, so CVS is the only option at the
Grzegorz Jaśkiewicz wrote:
On Fri, May 29, 2009 at 1:19 AM, Kris Jurka bo...@ejurka.com wrote:
To build against 8.4 you need pljava from CVS. Also pljava can only be
built with the 1.4 or 1.5 JDK, not with the 1.6 version you are using.
is it a lot of work to make it 1.6 friendly ?
can I
Grzegorz Jaśkiewicz wrote:
another question, what about tmdb ? it requires java6, so I assumed
that jdbc is 1.6 friendly odd.
I have no idea what tmdb is. JDK 1.6 includes the JDBC 4 API while
1.4 and 1.5 include the JDBC 3 API. So building pljava doesn't
implement all of the JDBC 4
Scott Marlowe wrote:
Douglas Alan wrote:
Okay -- no problem:
set enable_seqscan = on;
explain analyze select * from maindb_astobject
where survey_id = 2
limit 1000;
Limit (cost=0.00..48.03 rows=1000 width=78) (actual
time=84837.835..265938.258 rows=1000 loops=1)
-
Good point, I should have specified 8.3.7.
Just one more reason to anxiously anticipate upgrading to 8.4.
On 30/05/2009, at 2:56 AM, Tom Lane wrote:
Adam Ruth adamr...@mac.com writes:
Always test your performance assumptions. The plpgsql function is
faster than the sql function, a lot
Apparently this did not go through the first time. I'll try again.
Bayless Kirtley wrote:
Thanks Tom and Scott. You got me looking in the right direction. In this
case
the client and server are on the same machine (testing/development) and
psql
does return the right result. I tried all
Thanks for all the replies. I'm going to post the results of using
the recommended approach in another thread.
On Fri, May 29, 2009 at 1:18 PM, Adam Ruth adamr...@mac.com wrote:
Good point, I should have specified 8.3.7.
Just one more reason to anxiously anticipate upgrading to 8.4.
On
Based on replies to another post (recommending use of
'generate_series'), I was able to write the following query that
returns all paramters of a given function.
Only one interesting thing to note- in order to return the proper
argument type, I had to use
proargtypes[i - 1] when I expected
Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.
Scott
--
Sent via pgsql-general mailing list
You can establish a new connection without dropping the old one. Thus you
can be connected to 2 databases at the same time. Just use the appropriate
connection for the database you want.
Bayless
- Original Message -
From: Scott Bailey arta...@comcast.net
To: pgsql-general
conceptually if you consider the DB parameters which are necessary to establish
a SQL connection
you would need to accomodate 'BOTH' sets of these DB parameters
JDBC spec does'nt address the need to 'switch' so Type4 JDBC driver writers
concentrated on one connection to one database using a
Scott Bailey wrote:
Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.
the \connect command disconnects from the current
does '\connect' work with Type-IV JDBC Database?
can you effectively 'drop' the first DB connection
and somehow read a different set of DB parameters to connect to a new DB?
thanks,
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni
Martin Gainty wrote:
does '\connect' work with Type-IV JDBC Database?
can you effectively 'drop' the first DB connection
and somehow read a different set of DB parameters to connect to a new DB?
\connect is a psql command, nothing to do with JDBC.
In Java, you would Close() a JDBC
true
scott was asking if you can utilise the same JDBC Type IV DB connection for both
the original DB connection and utilise that same connection to connect to
another
DB (with a fresh set of database parameters)
my answer is i do not know of a way to use the same connection for 2 different
Martin Gainty wrote:
true
scott was asking if you can utilise the same JDBC Type IV DB
connection for both
the original DB connection and utilise that same connection to connect
to another
DB (with a fresh set of database parameters)
my answer is i do not know of a way to use the same
John R Pierce wrote:
Scott Bailey wrote:
Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.
the \connect command
Marcos Davi Reis wrote:
Hello All,
I did install the pgsql 8.3.7 on Ubuntu 8.04 using apt-get, now i need to
install pldebugger (edb-debugger) to write complex stored procedures.
I can't speak for the debugger specifically as I haven't built/tested
it, but:
In general, you'd install the
inf200...@ucf.edu.cu wrote:
I need to copy a data base from
windows to linux, how can I save my data base from windows with pg_dump, and
where the
file is??
and after how can I create the data base in linux without graphic
enviroment , just text mode, with commands
You might be more
Kris Jurka wrote:
Grzegorz Jaśkiewicz wrote:
On Fri, May 29, 2009 at 1:19 AM, Kris Jurka bo...@ejurka.com wrote:
To build against 8.4 you need pljava from CVS. Also pljava can only be
built with the 1.4 or 1.5 JDK, not with the 1.6 version you are using.
is it a lot of work to make it 1.6
Craig Ringer wrote:
Perhaps a stupid question, but isn't the `-source' parameter to javac
intended to mask new features and such for just the purpose of compiling
older sources on a new JDK?
The -source argument only controls language features, not
interface/class definitions. java.sql.*
58 matches
Mail list logo
