[SQL] How to create an aggregate?
Not sure if this made it through before I subscribed, so...
Hello all,
before I ask, this is what I have done so far:
-- created this new aggregate function
CREATE AGGREGATE groupconcat_array (
SFUNC = array_append,
BASETYPE = anyelement,
STYPE = anyarray,
initcond = '{}'
);
-- ran this select statement
SELECT array_to_string(groupconcat_array(oa.order_number), '|') ...
output is text : 46952|46953|46954|46955|46949
What I would like to do is just have 1 function that does the same thing like:
SELECT groupjoin('|', field2) FROM mytable GROUP BY field1
with the same output as my current implementation.
I tried doing that with the CREATE AGGREGATE in conjunction with the
FINALFUNC parameter set to array_to_string, but array_to_string needs
2 parameters to function. and I do not know the reference name of the
STYPE variable while it is in the aggregate function to pass to it. I
also would like to pass the delimiter to the aggregate as a parameter
and I am not sure if it can handle that.
I know that this is just being picky, but any insight would be
appreciated. Thanks.
Ray A.
--
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to create an aggregate?
So the AGGREGATE function also references parameters like a regular SQL function. Good to know. Figured I'd ask anyway. Thanks for the info Tom, Greg. Ray A. > I also would like to pass the delimiter to the aggregate as a parameter and I am not sure if it can handle that. It can't. You'll need a single-argument finalfunc that hardwires the delimiter, ie, array_to_string($1, '|') regards, tom lane -- Ray Aspeitia Sells Printing Company LLC [EMAIL PROTECTED] (262) 317-8314 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Query execution differences
Hello,
I am running into some runtime differences that do not seem to make
sense and would like some help interpreting the EXPLAIN ANALYZE
output.
I have run 2 identical queries, one for each company. Company 1
returns 628 records in a time of 674ms and Company 2 returns 73
records in a time of 4051ms.
Because of the table structure it is possible that Company 2 could
have more rows in specific queries but not at anywhere near the
records currently in the system.
I am including/attaching the explain analyze output for both
companies, it's kind of wide so I hope the email servers don't munge
the pasted version. Some info on what each section does would help or
if someone knows of a reference I could check out would work too. I
checked the Docs on explain but did not see anything that helped me
out.
Thanks.
Ray A.
Sort (cost=2323.82..2323.91 rows=35 width=78) (actual
time=650.345..650.775 rows=628 loops=1)
Sort Key: oa.date_created
-> Hash Left Join (cost=2304.35..2322.92 rows=35 width=78)
(actual time=609.815..646.398 rows=628 loops=1)
Hash Cond: ("outer".order_number = "inner".order_number)
-> Merge Left Join (cost=2141.36..2159.22 rows=35 width=66)
(actual time=568.392..595.386 rows=628 loops=1)
Merge Cond: ("outer".order_number = "inner".order_number)
-> Merge Left Join (cost=1920.33..1927.06 rows=35
width=58) (actual time=489.753..504.345 rows=628 loops=1)
Merge Cond: ("outer".order_number = "inner".order_number)
-> Sort (cost=1490.02..1490.11 rows=35
width=50) (actual time=339.998..340.466 rows=628 loops=1)
Sort Key: oa.order_number
-> Nested Loop (cost=1320.09..1489.13
rows=35 width=50) (actual time=271.391..332.113 rows=628 loops=1)
-> Merge Join
(cost=1320.09..1335.74 rows=39 width=44) (actual
time=270.675..300.962 rows=628 loops=1)
Merge Cond:
("outer"."?column4?" = "inner"."?column8?")
-> Sort (cost=873.62..880.80
rows=2874 width=18) (actual time=178.135..180.123 rows=2714 loops=1)
Sort Key: (ci.storeno)::text
-> Seq Scan on
customer_information ci (cost=0.00..708.52 rows=2874 width=18)
(actual time=0.264..119.093 rows=2781 loops=1)
Filter:
('deere'::text = (company_name)::text)
-> Sort (cost=446.47..446.91
rows=177 width=51) (actual time=92.411..92.970 rows=628 loops=1)
Sort Key: (oa.storeno)::text
-> Seq Scan on
order_admin oa (cost=0.00..439.86 rows=177 width=51) (actual
time=2.634..77.551 rows=628 loops=1)
Filter:
(((order_status)::text = 'completed'::text) AND ((company_name)::text
= 'deere'::text) AND ((group_code)::text = '2005'::text))
-> Index Scan using
order_address_pkey on order_address oad (cost=0.00..3.92 rows=1
width=10) (actual time=0.029..0.033 rows=1 loops=628)
Index Cond:
("outer".order_number = oad.order_number)
-> Sort (cost=430.30..433.56 rows=1302
width=12) (actual time=144.569..146.378 rows=2436 loops=1)
Sort Key: impn.order_number
-> Subquery Scan impn
(cost=346.67..362.95 rows=1302 width=12) (actual
time=109.041..129.276 rows=2436 loops=1)
-> HashAggregate
(cost=346.67..349.93 rows=1302 width=8) (actual time=109.025..119.439
rows=2436 loops=1)
-> Hash Join
(cost=17.98..340.16 rows=1302 width=8) (actual time=24.299..92.981
rows=2644 loops=1)
Hash Cond:
("outer".item_id = "inner".item_id)
-> Seq Scan on
order_items oi (cost=0.00..279.15 rows=6002 width=8) (actual
time=0.203..64.595 rows=5902 loops=1)
Filter: (has_imprint = true)
-> Hash
(cost=17.89..17.89 rows=36 width=12) (actual time=2.991..2.991 rows=0
loops=1)
-> Hash Join
(cost=14.07..17.89 rows=36 width=12) (actual time=2.522..2.862
rows=36 loops=1)
Hash Cond:
("outer".item_id = "inner".item_id)
-> Seq Scan
on items_imprint ii (cost=0.00..1.36 rows=36 width=8) (actual
time=0.084..0.178 rows=36 loops=1)
-> Hash
(cost=13.66..13.66 rows=166 width=4) (actual time=2.319..2.319 rows=0
