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] Problems with UNION ALL and ORDER BY
Dear friends..., I have the following problem: select . from where UNION ALL select ... from where ORDER BY field1 But the the order by doesn't work properly. It returns the rows of the first query ordered and then appends the rows of the second query ordered. But this is not what i expect. I expect the result of both queries to be orderd. So I try. SELECT TEMP.* FROM ( select . from where UNION ALL select ... from where ) TEMP ORDER BY TEMP.field1 But this also doesn't work. Any ideas.p ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Problems with UNION ALL and ORDER BY
Hello again, I am using Postgres 7.2.3. If any other details are necessary I will provide them:))) 10x again ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Problems with UNION ALL and ORDER BY
Dear Tom, As I say in my previous letter I am using 7.2.3. If you wish I can show you the query and the result to see for yourself, that there is something wrong. It just don't order the overall result but the separate results of the both subqueries. Tom Lane wrote: Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: I have the following problem: select . from where UNION ALL select ... from where ORDER BY field1 But the the order by doesn't work properly. It returns the rows of the first query ordered and then appends the rows of the second query ordered. Pray tell, what Postgres release are you using? AFAICT this will result in an overall sort in all PG releases since 7.0. I don't have anything older to test... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Problems with UNION ALL and ORDER BY
Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: > I have the following problem: > select > . > from > > where > > UNION ALL > select > ... > from > > where > > ORDER BY field1 > But the the order by doesn't work properly. It returns the rows of the > first query ordered and then appends the rows of the second query > ordered. Pray tell, what Postgres release are you using? AFAICT this will result in an overall sort in all PG releases since 7.0. I don't have anything older to test... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Problems with UNION ALL and ORDER BY
Kaloyan Iliev Iliev wrote: Dear friends..., I have the following problem: select . from where UNION ALL select ... from where ORDER BY field1 But the the order by doesn't work properly. It returns the rows of the first query ordered and then appends the rows of the second query ordered. But this is not what i expect. I expect the result of both queries to be orderd. So I try. SELECT TEMP.* FROM ( select . from where UNION ALL select ... from where ) TEMP ORDER BY TEMP.field1 But this also doesn't work. Any ideas.p This have to work. Could you please provide a real example ( creation table, insertion data and query execution ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Problems with UNION ALL and ORDER BY
Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: > As I say in my previous letter I am using 7.2.3. If you wish I can show > you the query and the result to see for yourself, that there is > something wrong. It just don't order the overall result but the separate > results of the both subqueries. Quite honestly, I don't believe it. In 7.2 I get regression=# explain select * from tenk1 a union all select * from tenk1 b order by unique1; NOTICE: QUERY PLAN: Sort (cost=3128.28..3128.28 rows=2 width=148) -> Append (cost=0.00..760.00 rows=2 width=148) -> Subquery Scan *SELECT* 1 (cost=0.00..380.00 rows=1 width=148) -> Seq Scan on tenk1 a (cost=0.00..380.00 rows=1 width=148) -> Subquery Scan *SELECT* 2 (cost=0.00..380.00 rows=1 width=148) -> Seq Scan on tenk1 b (cost=0.00..380.00 rows=1 width=148) EXPLAIN and as you can see there's only one sort step being applied to the union result. Now if EXPLAIN shows you a different sorting structure for your query, then I'd be interested to see the exact query and the EXPLAIN output. But what I think is that you are misinterpreting the sorting result you get. If you are using a non-C locale you may be seeing some pretty weird sorting rules :-( regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Problems with UNION ALL and ORDER BY
10x for the replies. We find the problem. It is in our scripts. We use to_char over a date field and then order by this field. So it sort it as text and not as date. I appologize for loosing your time. 10x again. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:143)
On Mon, 2 Aug 2004, [iso-8859-1] Smita Marda wrote: > Hi, I have been using SOFIA framework and postgres database as a > backend. i noticed as the database started growing larger.. today > encountered some error while firing a query to save a record. The table > in which i am going to save the record, has two columns having datatype > text. the data of these two column contains 2652 and 5817 characters. If > I reduce some characters from both columns, it saves the record > successfully. But i reduce some characters from only single > column(whether it has 2652 chars or 5817 chars whereas both have the > same datatype), it gives me the following error :-- > > The backend has broken the connection. Possibly the > action you have attempted has caused it to close. > at > org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:143) > at This looks like it could be a server crash. Does the server's log show anything informative? Also you don't mention what server and JDBC driver version you are using, but the stacktrace tells me the driver must be from the 7.2 or earlier release. If the server log doesn't reveal anything you might try upgrading the JDBC driver to a later version. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[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
[SQL] Trigger and function not on speaking terms
Hmm, this is puzzling me:
create or replace function fn_foo(text) returns trigger as '
begin
# Do some stuff with $1
end;
' language 'plpgsql';
CREATE FUNCTION
create table bar (aaa text);
CREATE TABLE
create trigger trg_bar
after insert or update on bar
execute procedure fn_foo('string');
ERROR: function fn_foo() does not exist
It would seem my trigger definition is trying to find fn_foo(), when I
mean for it to call fn_foo(TEXT).
--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
Jeffery Boes <>< [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Trigger and function not on speaking terms
On Mon, 2 Aug 2004, Jeff Boes wrote:
> Hmm, this is puzzling me:
>
> create or replace function fn_foo(text) returns trigger as '
> begin
># Do some stuff with $1
> end;
> ' language 'plpgsql';
>
> CREATE FUNCTION
>
> create table bar (aaa text);
>
> CREATE TABLE
>
> create trigger trg_bar
> after insert or update on bar
> execute procedure fn_foo('string');
>
> ERROR: function fn_foo() does not exist
>
> It would seem my trigger definition is trying to find fn_foo(), when I
> mean for it to call fn_foo(TEXT).
I don't remember why, but the arguments from the create trigger statement
are passed differently from standard arguments (I think it's like TGARGS
in plpgsql).
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Trigger and function not on speaking terms
On Mon, Aug 02, 2004 at 04:20:15PM -0400, Jeff Boes wrote: > It would seem my trigger definition is trying to find fn_foo(), when I > mean for it to call fn_foo(TEXT). Triggers have to be declared to take no arguments; they find the rows on which they operate in magical ways. (For PL/PgSQL triggers, see chapter 37.10 of the manual.) Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
