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 wrote:
> Good point, I should have specified 8.3.7.
>
> Just one more reason to anxiously anticipate upgrading to 8.4.
>
>
>
> On 30/05/2009,
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 writes:
Always test your performance assumptions. The plpgsql function is
faster than the sql function, a lot faster on smaller arr
Adam Ruth 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 8.4.
Using CVS
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 .
Adam Ruth 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 ;-)
2009/5/29 Grzegorz Jaśkiewicz :
> 2009/5/29 Adam Ruth :
>> 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: 1
2009/5/29 Adam Ruth :
> 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 arr
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
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 generate_series(array_lower($1,1),array_up
:11 AM GMT -08:00 Tijuana / Baja
California
Subject: [GENERAL] Converting each item in array to a query result row
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 e
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
- Original Message -
From: "Postgres User"
To: "pgsql-general"
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 a query against a function
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 f
13 matches
Mail list logo
