Re: [SQL] function defination help ..
Thank you i will look into its source code. and try to find some solution for myself. regds mallah. On Saturday 22 February 2003 07:40 am, Peter Eisentraut wrote: > Rajesh Kumar Mallah writes: > > is it possible to get the function creation defination as produced by > > pg_dump by some SQL queries on system catalogs? > > > > pg_func stores procsrc but i am trying to get RETURNS and the arg part > > also. > > You will need to reconstruct what pg_dump does. -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] [BUGS] 7.3 GROUP BY differs from 7.2
On Fri, 21 Feb 2003, Tom Lane wrote: > Dan Langille <[EMAIL PROTECTED]> writes: > > This is the query in question: > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > FROM watch_list JOIN watch_list_element > >ON watch_list.id = watch_list_element.watch_list_id > > WHERE watch_list.user_id = 1 > > GROUP BY watch_list_element.element_id; > > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in an > > aggregate function > > The parser currently considers an output column of a JOIN to be a > different variable from the corresponding column of the input table. > Thus the above error message. While the distinction is without content > in this example, it is extremely real in some nearby cases --- in > particular, in NATURAL or USING full outer joins it's possible for one > to be null when the other isn't. (And no, I don't think 7.2 got this > right.) > > I'm having a hard time finding anything in the SQL spec that addresses > this point specifically --- but I also cannot find anything that > suggests that the name scope rules differ between outer and inner joins. > So it would be difficult for them to assert that element_id and > watch_list_element.element_id must be treated as equivalent here, > when they are clearly not equivalent in related cases. > > Anyone care to offer a gloss on the spec to prove that this behavior > is correct or not correct? Hmm, I'd read SQL92 Section 7.5 (joined tables) Syntax Rules as saying that the non natural/using case is separate from the other cases. Syntax rule 5 seems to say that A JOIN B ON (...) should have the same column descriptors as A,B and it explicitly doesn't cover NATURAL or USING (covered by rule 6). ---(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] [BUGS] 7.3 GROUP BY differs from 7.2
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Fri, 21 Feb 2003, Tom Lane wrote: >> Anyone care to offer a gloss on the spec to prove that this behavior >> is correct or not correct? > Hmm, I'd read SQL92 Section 7.5 (joined tables) Syntax Rules as saying > that the non natural/using case is separate from the other cases. > Syntax rule 5 seems to say that A JOIN B ON (...) should have the same > column descriptors as A,B and it explicitly doesn't cover NATURAL or > USING (covered by rule 6). Yeah, but those rules only define the names and types of the JOIN's output columns. They don't say anything about the visibility of those names, nor the visibility of the underlying-table column names, nor particularly about semantic equivalence of the two sets of names. I spent some time digging around in the verbiage about name scopes, but didn't find any joy. It does seem clear that if you stick an alias on the JOIN as a whole, that that hides the individual table names/aliases, which would render the issue moot. But Dan didn't do that in his example, so he is allowed to access both the join columns and the underlying columns. Question is, are they equivalent for the purposes of the grouped-column-reference rules, or not? 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] 7.3 "group by" issue
Peter, > This does not work because the first select list item references a column > inside a join, which is not (necessarily) mathematically identical to the > column that arrives outside of the join and is in the GROUP BY clause. > (Think of an outer join: the column outside the join might contain added > null values. Of course you are using an inner join, but the constructs > work the same either way.) Hmmm ... I don't see that. I can see that it might be practically difficult-to-impossible to make the planner distinguish between cases where the columns in the select list are different from the columns in the JOIN series and when they aren't, but as a theory issue I just don't buy it. What Dan's doing is: SELECT a.c1, count(a.c2) FROM a JOIN b WHERE b.c5 = x GROUP BY c1 In a case like this, a.c1 == c1 without possibility of ambiguity. The only difference is whether or not a table qualifier is used on the name, which in theory should *only* make a difference when there are more than one table in the query with that column name. So if you're saying that that kind of equivalency is a challenging parser implementation issue, then I'm with you. If you're saying its a SQL theory issue, though, I don't agree at all. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sql question after upgrade
Hi just upgraded to 7.3.2 and imported my db schema with no probs! Just wondering... why VOLATILE is being appended to my functions - can anyone ansewer me what this means? - should i be using joins instead, does this make a difference? If so an example of this would be much appreciated!! CREATE FUNCTION public.get_people_total(int4) RETURNS int8 AS 'select count(person_id) as total from person where person_id IN(select cp.person_id from cluster_person cp, cluster c where cp.cluster_id = c.cluster_id and c.c_id = $1 group by cp.person_id );' LANGUAGE 'sql' VOLATILE; cheers all James
Re: [SQL] [BUGS] 7.3 GROUP BY differs from 7.2
On Sat, 22 Feb 2003, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Fri, 21 Feb 2003, Tom Lane wrote: > >> Anyone care to offer a gloss on the spec to prove that this behavior > >> is correct or not correct? > > > Hmm, I'd read SQL92 Section 7.5 (joined tables) Syntax Rules as saying > > that the non natural/using case is separate from the other cases. > > > Syntax rule 5 seems to say that A JOIN B ON (...) should have the same > > column descriptors as A,B and it explicitly doesn't cover NATURAL or > > USING (covered by rule 6). > > Yeah, but those rules only define the names and types of the JOIN's > output columns. They don't say anything about the visibility of those > names, nor the visibility of the underlying-table column names, nor > particularly about semantic equivalence of the two sets of names. > > I spent some time digging around in the verbiage about name scopes, > but didn't find any joy. It does seem clear that if you stick an > alias on the JOIN as a whole, that that hides the individual table > names/aliases, which would render the issue moot. But Dan didn't > do that in his example, so he is allowed to access both the join > columns and the underlying columns. Question is, are they equivalent > for the purposes of the grouped-column-reference rules, or not? Well, I mentioned it because I thought that it might mean that the semantic equivalence for columns would be the same as the A,B case (barring aliases, using, etc) since the only rule about the visibility on the names in that case I could find was that you can't qualify to a table within a joined table on a "common column name" of the joined table. I'd think the rule would have to be the same for the A,B case as well. And, if say A.A and A are ambiguous on whether they're the same, wouldn't the rules on group by say that A.A is invalid grouping column reference in this case whether or not the select list has A or A.A? I'm thinking that it wouldn't be an unambiguous reference to a column of the preceding FROM/WHERE clause. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
