Fwd: Re[2]: [SQL] We all are looped on Internet: request + transport = invariant
---(end of broadcast)--- TIP 1: 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
Fwd: Re[2]: [SQL] We all are looped on Internet: request+transport = invariant
Good day, Bart. > I'll continue with the analogy Let's suppose, that you want to compare rational and hierarchical DBMS instead of discussion about removing gaskets (by the way, hierarchical DBMS also need gasket between itself and external world). Because TML should integrated inside rational DBMS, I think, this comparison is useful too. First, let's accept convention to not use metaphore. It would be better, if we compare concrete details. Bart, under your influence i want to download and try hierarchical DBMS, which you list (Adabas, GT.M., IMS, DMSII, Focus, Metakit). That is so much important, because my wish to add TML to PG is growing :) Excuse me, Bart, our institution use only OS "Windows", thus at first stage let conduct comparison between TML and hierarchical DBMS only for "Windows". Several revisions: 1. "Adabas" is this http://www.softwareag.com/Corporate/products/adabas/download/default.asp (ada51.zip, 31Mb)? 2. "GT.M" exists only form Unix-like OS. 3. "IMS, DMSII, Focus" - i don't find link to download 4. "Metakit" is this http://www.softlookup.com/download.asp?id=6904 (metakit-2.01.tar.gz, 607 Kb). Our users consider, that it is no database in general. I agree with them. Are you agree to make comparison in discussion with me? Dmitry Turin http://html6.by.ru http://sql4.by.ru http://computer2.by.ru ---(end of broadcast)--- TIP 6: explain analyze is your friend
Fwd: Re[2]: [SQL] We all are looped on Internet: request + transport = invariant
Good day, Joe. >> >I would change your examples to use less abstract >> > data, like department/employee, customer/product/order/order_line J> I contend that then you'd find more people J> receptive to your ideas or at least able to criticize them from more J> concrete viewpoints. I expected, that you already read http://sql4.by.ru/site/sql40/en/author/wave_eng.htm (i added output into this paper). Case of "department/employee" is "Example 1" on this paper. Dmitry Turin http://html6.by.ru http://sql4.by.ru http://computer2.by.ru ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] How to get text representation of composite value?
Hello,
I'd like to ask you for a help. I'm in trouble with $subj. I need something
like documentation of PostgreSQL in chapter 8.11.5 says.
Inside trigger I have record type NEW which is composite type. How could one
do something like :
text_var:=to_char(NEW)
So if NEW.at1='test' and NEW.at2=100 I would get string: 'test',100
But the problem is then if trigger is generally used for many tables, I
don't know number of elements and so on.
As I know, for element types are defined output function, and I can use this:
SELECT textin(point_out('(1,1)'::point))::varchar;
Anybody can help me?
P.S. pl/pgsql would be best way if it possible.
David F
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] We all are looped on Internet: request + transport = invariant
Good day, Joe. J> "Fictional" is IMO not a good choice for J> describing aggregates, because it means "a story that is not true", J> whereas an SQL aggregate is something that is "true" (in the J> mathematical sense) but is "derived" from other values. It means, that TML and DDL aggregates have different sense. J> (I'm not sure what the special symbols mean -- maybe what you need first J> is a syntax reference for TML) http://sql4.by.ru/site/sql40/en/author/tree_eng.htm J> sum of the employees salaries? yes. "amount" is quantity of employees J> How do I see employees in just one department? department[id="1"].employee >>; or department[name="Technical"].employee >>; J> How do I see a single employee? employee[id="31"] >>; or employee[name="Tomson"] >>; -- maybe several employee with surname Tomson J> How do I see all employees that have a salary greater than 3500 department.employee[salary>3500] >>; J> ordered by their names? Now sorting for output is absent. I'm not sure, that it's necessary, but i already thought about this and continue to think. J> How do I actually retrieve SUM(salary) Declare field "sum" as not fictional. You can always declare field "sum" as not fictional and put sign "#" before field to prevent output of field. J> GROUP BY department? It is always "group by", because it is field of record "department" P.S. If you don't like to put sign "#" before field "sum", or you don't like absence of sorting NOW (it's possible, that i add sorting), then remember, that advantage is absence of gasket (php, etc) between DBMS and external world. That is more important for un-experienced users. Dmitry Turin http://html6.by.ru http://sql4.by.ru http://computer2.by.ru ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Fwd: Re[2]: [SQL] We all are looped on Internet:request+transport = invariant
BD> Please stop using my personal email adsress in CC or BCC. BD> Forum related mails should only be send to the forum! I promise BD> Several revisions: BD> 1. "Adabas" is this BD> http://www.softwareag.com/Corporate/products/adabas/download/default.asp BD> (ada51.zip, 31Mb)? BD> 2. "GT.M" exists only form Unix-like OS. BD> 3. "IMS, DMSII, Focus" - i don't find link to download BD> 4. "Metakit" is this BD> http://www.softlookup.com/download.asp?id=6904 BD> (metakit-2.01.tar.gz, 607 Kb). BD> Our users consider, that it is no database in general. BD> I agree with them. BD> Are you agree to make comparison in discussion with me? And what's about comparison ? Dmitry Turin http://html6.by.ru http://sql4.by.ru http://computer2.by.ru ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] hi
Hi I have the data like this in temp table SQL> Select sno, value from temp; SNO Value 1 650.00 2 850.00 3 640.00 3 985.00 5 987.00 9 9864.00 7 875.00 Now, I want display the result like this... SQL> Select sno, value, get_sum(value) as sum from temp; SNO Value SUM 1 650.00 650.00 2 850.00 1500.00 --650+850 3 640.00 2140.00 --1500+640 3 985.00 3125.00 -- 2140+985 5 987.00 4112.00 9 9864.00 13976.00 7 875.00 14851.00 Any one can tell me query for this .. I don't want any procedure or function... Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: Fwd: Re[2]: [SQL] We all are looped on Internet: request + transport = invariant
Dmitry Turin wrote: Good day, Joe. I would change your examples to use less abstract data, like department/employee, customer/product/order/order_line J> I contend that then you'd find more people J> receptive to your ideas or at least able to criticize them from more J> concrete viewpoints. I expected, that you already read http://sql4.by.ru/site/sql40/en/author/wave_eng.htm (i added output into this paper). Case of "department/employee" is "Example 1" on this paper. I'd not expect people to read lengthy articles on a general-questions mailing list, but I've skimmed at least as far as example 2. 1. What does it mean for one flight to be contained within another? I can see how one flight might follow another, but not contained. Do you not need some new object "flight_chain" or similar? 2. Surely a flight should contain two cities (since it connects two cities). Alternatively, the needs to indicate to which it's referring either as / or or similar. 3. How am I constructing these queries? The whole point was so I didn't have to learn SQL, yes? Unfortunately, the syntax for these queries isn't obvious enough to me that I can see how to write them without some form of query-builder, or reference manual. 4. How am I using these queries? I'm still not clear what use it is to have XML without a schema. Let's say I want to build a holiday website. How does this TML setup avoid me having to write any php/perl/etc? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] hi
Select sno AS "SNO", SELECT value AS "VALUE", get_sum(value) as SUM from temp; From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah P. Sent: April 24, 2007 8:09 AM To: [email protected] Subject: [SQL] hi Hi I have the data like this in temp table SQL> Select sno, value from temp; SNO Value 1 650.00 2 850.00 3 640.00 3 985.00 5 987.00 9 9864.00 7 875.00 Now, I want display the result like this... SQL> Select sno, value, get_sum(value) as sum from temp; SNO Value SUM 1 650.00 650.00 2 850.00 1500.00 --650+850 3 640.00 2140.00 --1500+640 3 985.00 3125.00 -- 2140+985 5 987.00 4112.00 9 9864.00 13976.00 7 875.00 14851.00 Any one can tell me query for this .. I don't want any procedure or function... Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] hi
Hi, try this. It's working... SELECT t1.sno, sum(t2.value) FROM temp as t1, temp as t2 WHERE t1.sno >= t2.sno GROUP BY t1.sno; but not completely. The problem is the this suppose your sno is uniqu. But as I see 3 is not. Anyway it may help you. >I have the data like this in temp table > >SQL> Select sno, value from temp; > >SNO Value > >1 650.00 > >2 850.00 > >3 640.00 > >3 985.00 > >5 987.00 > >9 9864.00 > >7 875.00 > >Now, I want display the result like this... > >SQL> Select sno, value, get_sum(value) as sum from temp; > >SNO Value SUM > >1 650.00 650.00 > >2 850.00 1500.00 --650+850 > >3 640.00 2140.00 --1500+640 > >3 985.00 3125.00 -- 2140+985 > >5 987.00 4112.00 > >9 9864.00 13976.00 > >7 875.00 14851.00 > > > >Any one can tell me query for this .. I don't want any procedure or >function... > > > > > >Thanks & Regards > >Penchal Reddy > > > > > >Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] We all are looped on Internet: request + transport = invariant
Hi Dmitry,
On Tue, 2007-04-24 at 15:31 +0300, Dmitry Turin wrote:
> J> How do I see employees in just one department?
>
> department[id="1"].employee >>;
>
> or
>
> department[name="Technical"].employee >>;
How is that any different or better than a standard SQL SELECT (or to
use another context, a Tutorial D statement)?
> J> How do I see a single employee?
>
> employee[id="31"] >>;
>
> or
>
> employee[name="Tomson"] >>;
>-- maybe several employee with surname Tomson
What if I want to see specific columns of the employee row or see the
name of the department that Tomson works in or the name of Tomson's
manager?
> J> How do I actually retrieve SUM(salary)
>
> Declare field "sum" as not fictional.
>
> You can always declare field "sum" as not fictional and
> put sign "#" before field to prevent output of field.
This "fictional" attribute of yours implies a change to SQL DDL.
> J> GROUP BY department?
>
> It is always "group by", because it is field of record "department"
That is only in *your* view of the data. What if someone comes along
and wants to do SELECT name, SUM(salary) FROM employee GROUP BY name?
Or GROUP BY date_trunc('year', hire_date)?
The point is that SQL has been refined (although some may disagree :-)
over the past quarter century to answer this and many other questions
and I don't see how TML improves on that (for an enhanced answer to SQL,
take a look at Tutorial D). And XML is *not* the only answer to viewing
or even "transporting" data. Some people prefer a tabular
representation, others may prefer a hierarchical view, and yet others
want to see a chart (and even the same person may prefer a different
view for various purposes or at various times).
And there's nothing wrong with Perl, PHP, Python and the myriad
interface languages. That's what "free" software is all about. The
POSTGRES UCB project had a language called PostQUEL, which may have been
technically superior to SQL, but market forces (unfortunately not all
"free") pushed Postgres95 and then PostgreSQL to adopt the latter in
preference to PostQUEL. Maybe one day we'll have one (or two) interface
languages to PostgreSQL that 80% of the users will use, but it will not
be because you or I or even the PG Global Dev Group leaders say it's
"better" or "it's necessary", but because thousands of users may agree
and converge on those choices.
Joe
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] hi
On þri, 2007-04-24 at 17:39 +0530, Penchalaiah P. wrote: > Hi > > I have the data like this in temp table > > SQL> Select sno, value from temp; you seem to be assuming a specific order for this. gnari ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Function to return a multiple colmn table or view
Don't forget to cc: the list Wilkinson, Jim wrote: Hi Richard, your example worked find , but when I substitue my view into the function and the tale name, I get the following error: ERROR: wrong record type supplied in RETURN NEXT CONTEXT: Pl/pqSQL function "create_view" line 11 at return next The columns you're returning from the function don't match it's definition. If the function is defined as RETURNS SETOF X then it needs to return columns that match X. If you need to you can create a custom type (see "CREATE TYPE" in the manuals). If you want one function that returns different types then you can't have it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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] hi
On Tue, 24 Apr 2007, Penchalaiah P. wrote: > Hi > > I have the data like this in temp table > > SQL> Select sno, value from temp; > > SNO Value > > 1 650.00 > > 2 850.00 > > 3 640.00 > > 3 985.00 > > 5 987.00 > > 9 9864.00 > > 7 875.00 Tables are not ordered. You'll need something like an ordering column that represents the ordering and is unique. Then you can probably do something like (untested): select sno, value, (select sum(value) as sum from temp t where t.ordering <= temp.ordering) from temp order by ordering; or select t1.sno, t1.value, sum(t2.value) from temp as t1, temp as t2 where t1.ordering >= t2.ordering group by t1.ordering, t1.sno, t1.value order by t1.ordering; ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] plpgsql array looping
I am attempting to create a moving average elements function that will
return a set of elements in a particular moving average and am having
difficulty iterating thrrough the passed in array properly. Any help
would be greatly appreciated.
code below...
select getmovavgelements( aggarray(trade_date), aggarray(close_price),
'2004-02-10'::timestamp, 10 )
from
( select trade_date, close_price::numeric
from quotedata
where symbol='MSFT'
and trade_date > '2004-01-01'
order by trade_date asc) values;
NOTICE: idx: {"2004-01-02 00:00:00","2004-01-05 00:00:00",...}
NOTICE: vals: {27.45,28.14,...}
NOTICE: maxdate: 2004-02-10 00:00:00
NOTICE: dims: [1:821]
NOTICE: idx ptr: 2
NOTICE: idx ptr: 4
NOTICE: idx ptr: 6
NOTICE: idx ptr: 8
NOTICE: idx ptr: 10
NOTICE: idx ptr: 12
NOTICE: idx ptr: 14
NOTICE: idx ptr: 16
NOTICE: idx ptr: 18
NOTICE: idx ptr: 20
NOTICE: idx ptr: 22
NOTICE: idx ptr: 24
NOTICE: idx ptr: 26
NOTICE: row: 2004-01-02 00:00:00 27.45
ERROR: array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function "getmovavgelements" line 25 at assignment
---
CREATE OR REPLACE FUNCTION getmovavgelements(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8)
RETURNS setof resultset AS
$BODY$
declare
idxptr int8;
idxendptr int8;
v_rec record;
v_rtn resultset%rowtype;
v_sql text;
begin
raise notice 'idx: %', p_idxarray;
raise notice 'vals: %', p_valarray;
idxptr := array_lower(p_idxarray, 1);
raise notice 'maxdate: %', p_idx;
raise notice 'dims: % ', array_dims(p_idxarray);
for idxptr in 1 .. array_upper(p_idxarray, 1)
loop
exit when p_idxarray[idxptr] >= p_idx;
idxptr := idxptr +1;
raise notice 'idx ptr: %', idxptr;
end loop;
idxendptr := idxptr + p_periods;
for v_rec in
select s.ser, p_idxarray[s.ser] as index, p_valarray[s.ser] as
value
from generate_series(idxptr, idxendptr) as s(ser)
loop
raise notice 'row: % %', v_rec.index, v_rec.value;
v_rtn := (v_rec.index, v_rec.value);
return next v_rtn;
end loop;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
Rick Albright
Senior Quantitative Analyst
Indie Research, LLC
254 Witherspoon Street
Princeton, NJ 08542
(609)497-1030
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] Query Join Performance
I have a database with the following tables:
zip {
zip_id bigserial,
zip_cd varchar(6) -- this is indexed
}
city {
city_id bigserial,
city_name varchar(50)
}
zip_city {
city_id bigint (FK to city table),
zip_id bigint (FK to zip table),
longitude numeric,
latitude numeric
}
We want to find the zip_cd values for all zip codes within 50 miles of a
specified zip code and have this query so far (assume our given zip code is
64131):
select
zip_city.zip_id,
gps_distance(zip_city.longitude::numeric,
zip_city.latitude::numeric, my_location.longitude::numeric,
my_location.latitude::numeric) AS distance
from zip_city
inner join (
select
longitude,
latitude
from zip
inner join zip_city on (
zip.zip_id = zip_city.zip_id
)
where zip_cd = '64131'
) my_location on (
-- We use this as an approximation to filter out as many records as
possible before doing the gps_distance() function call
-- It works well as a outer bounds to get the most obvious outside
zip_city values.
abs(zip_city.longitude - my_location.longitude) <= (50.0 / 60.0)
AND abs(zip_city.latitude - my_location.latitude) <= (50.0 / 60.0)
)
This runs in about 2 seconds on Windows and gives me 650 records - not bad.
When I add just a little bit:
select
zip_city.zip_id,
gps_distance(zip_city.longitude::numeric,
zip_city.latitude::numeric, my_location.longitude::numeric,
my_location.latitude::numeric) AS distance
from zip_city
-- THE ONLY DIFFERENCE IS THE NEXT 3 LINES --
inner join zip on (
zip_city.zip_id = zip.zip_id
)
-- End of added code --
inner join (
select
longitude,
latitude
from zip
inner join zip_city on (
zip.zip_id = zip_city.zip_id
)
where zip_cd = '64131'
) my_location on (
abs(zip_city.longitude - my_location.longitude) <= (50.0 / 60.0)
AND abs(zip_city.latitude - my_location.latitude) <= (50.0 / 60.0)
)
Performance tanks with this query - it takes over 120 seconds (that is where
I set the timeout). I read that a foreign key doesn't help performance and
my guess is that PostgreSQL isn't just joining on the 650 records but rather
on the over 800,000 records in the zip table (yes we have that many - we
include Canada zip codes which is 90% of the data - Blame Canada! ;).
Is there something I can do to boost performance? I tried putting the first
query above in an inner join before joining with the zip table with the same
result.
BTW, on our Linux box the full query we run (which adds 3 more tables on the
whole operation along with more filtering on the zip table) finishes in
under 10 seconds. Problem is our development is on Windows and this is a
real pain for developers to test.
Any assistance is appreciated. I did look at the explain plans but I don't
see anything other than what I mentioned above. I can include those if
necessary.
Thanks!
Aaron
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
Re: [SQL] hi
Hi, Can i get GUI tool for PostgreSQLin LINUX.Pls let me know from wher i candownload the same. Regards SAJI VERGHESE +919941140433 - Original Message From: "Wilkinson, Jim" <[EMAIL PROTECTED]> To: Penchalaiah P. <[EMAIL PROTECTED]>; [email protected] Sent: Tuesday, 24 April, 2007 6:15:13 PM Subject: Re: [SQL] hi Select sno AS “SNO”, SELECT value AS “VALUE”, get_sum(value) as SUM from temp; From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah P. Sent: April 24, 2007 8:09 AM To: [email protected] Subject: [SQL] hi Hi I have the data like this in temp table SQL> Select sno, value from temp; SNO Value 1 650.00 2 850.00 3 640.00 3 985.00 5 987.00 9 9864.00 7 875.00 Now, I want display the result like this… SQL> Select sno, value, get_sum(value) as sum from temp; SNO Value SUM 1 650.00 650.00 2 850.00 1500.00 --650+850 3 640.00 2140.00 --1500+640 3 985.00 3125.00 -- 2140+985 5 987.00 4112.00 9 9864.00 13976.00 7 875.00 14851.00 Any one can tell me query for this .. I don’t want any procedure or function… Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. Add online presence to your web pages, find out how here - http://messenger.yahoo.com/addpresence.php
Re: [SQL] hi
Try pgAdmin - Google will find it for you. ~p On Wed, 2007-04-25 at 10:10 +0530, saji varghese wrote: > > Hi, > Can i get GUI tool for PostgreSQLin LINUX.Pls let me know from wher i > candownload the same. > ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
