Gj’s SQL blog

November 27, 2010

limited array_agg output on 8.3

Filed under: Uncategorized — Grzegorz Jaskiewicz @ 4:30 pm

Someone asked about it on postgresql-general mailing list. Here’s my way of doing it.

 

We need the limit_array_append() function. So here it is:

CREATE FUNCTION limit_array_append(anyarray, anyelement, integer) RETURNS anyarray AS
$_$
   SELECT (array_append($1, $2))[1:$3];
$_$ LANGUAGE sql IMMUTABLE;

The array_agg() function is obviously predefined on 8.3.

Than we need to define the aggregate:

CREATE AGGREGATE limit_array_agg(anyelement, integer)
(
   SFUNC = limit_array_append,
   STYPE = anyarray,
   INITCOND = '{}'
);

 

The result is beautiful limited array_agg. Same thing can be obviously done much better, both code wise, and performance wise on 8.4+ with window functions.

To test just do:

select limit_array_agg((random()*626)::integer, 3) FROM generate_series(1, 20) i GROUP BY (i/7)::integer; 
limit_array_agg 
----------------- 
{546,360,139} 
{411,287,339} 
{359,288,95}

(3 rows)

Easy, ain’t ?


Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: