Re: [SQL] We all are looped on Internet: request + transport = invariant
Hi Dmitry, On Wed, 2007-05-02 at 08:05 +0300, Dmitry Turin wrote: > J> The average man or woman on the street > > For what you say about street ? > Average people, which you can meet on street, make physical job. That is an American colloquialism to refer to just about anyone, regardless of what kind of work they do. The point is that --using the Pareto principle-- 80% (probably much more) of the people don't know SQL or are fluent in other programming languages, and they don't want to be bothered with *any* of it except to use the products and services that are made possible through them. According to the U.S. Bureau of Labor Statistics (BLS) there were 455,000 programming jobs in 2004. Even if you raise that by an order of magnitude you're still talking about less than 2% of the U.S. population. The BLS estimates there were 16,000 physicists/astronomers and 77, biological scientists. So the software/programming and scientist populations may be roughly comparable. While the ratios may be better in some other countries, I doubt that they're much off. The bottom line is: the markets for PHP/etc. and TML are not too large, but you seem to be having a hard time convincing those of us who've taken even a mild interest in TML that it's really needed or is a better solution than what exists today. Joe ---(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: Fwd: Re[2]: [SQL] We all are looped on Internet: request + transport = invariant
Dmitry Turin wrote: Good day, Richard. RH> With 7 flights it is easy to see if #2 matches #4. With 700 it is not so RH> easy to see #2 matches #504. With a tree-structure it is impossible to RH> sort leaf nodes without restructuring the tree. What is "#2 matches #4" ? Individual flights might occur in one or more options when building a flight-plan. What sorting are you imply ? None. To be in safe side: TML allow table as particular case of tree (when records of one table, i.e. nodes, have not sub-records, i.e. sub-nodes). RH> Never heard of eHTML, and I don't believe you can build a public-facing RH> website with it unless it's at least as complicated as php. I try http://lists.w3.org/Archives/Public/public-html/2007Apr/1384.html http://lists.w3.org/Archives/Public/public-html/2007Apr/1380.html Nothing there to build a website with - by which I mean the logic required for an interactive website. No. html-page send xml-data to TML-function "main". Without any PHP. RH> But it can't. It's HTML. You can't map arbitrary user-interface (e.g. RH> perhaps I want users to click on a map of the world to pick cities) to a RH> query language without some sort of glue language. You click on a map, and form send data to DBMS (DBMS listen port#80 and accept HTTP). What is impossible ? 1. HTML can't locate the nearest valid city based on where you clicked. HTML+Javascript could perhaps, but you didn't want people learning programming languages. 2. There isn't a database that listens on port 80 for HTTP 3. If there was, you'd still need it to translate a HTTP POST/GET into an internal query (in SQL or TML if there existed an implementation of TML). For non-trivial cases, that implies a programming language. 4. You'd still need logic to validate/transform input values and do the same for output values. For non-trivial cases that involves a programming language. RH> 5. PHP formats results TML extract xml to address of requester. RH> But no-one's going to want raw XML are they? They'll want properly RH> formatted results I agree. Results will formatted by CSS (or XSL). Even XSL is easier for scientists, than PHP-library. Why are scientists building a flight-planning website? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Dynamic prepare possible in plpgsql?
So if I have an INSERT inside a LOOP in a plpgsql function, it is only prepared once? Regards, Collin On 5/1/07, Jonah H. Harris <[EMAIL PROTECTED]> wrote: On 5/1/07, Collin Peters <[EMAIL PROTECTED]> wrote: > Is it faster to use PREPARE for the various INSERT statements inside a > plpgsql function? Perhaps I am wrong and it does its PREPARE work > when the function is parsed. IIRC, PLpgSQL automagically prepares each statement behind the scenes on the first use. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Possible to access value in RECORD without knowing column name?
In plpgsl, if I have a RECORD variable that is populated via some dynamic SQL, is it possible to access the columns in that RECORD object without knowing the column names? I.e. Can I grab whatever value is the 3rd column? random_colname = 'foobar'; sql = 'SELECT col1, col2, ' || random_colname || ' FROM table'; FOR mviews IN EXECUTE sql LOOP --possible to access 3rd column of mviews? something like mviews[3]? END LOOP; Regards, Collin ---(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] Possible to access value in RECORD without knowing column name?
my_column = 'foo' sql = 'select col1, col2, ' || my_column || ' as bar from mytable; for myrecord in execute sql loop myvariable = myrecord.bar end loop; On Wed, 2007-05-02 at 12:17 -0700, Collin Peters wrote: > In plpgsl, if I have a RECORD variable that is populated via some > dynamic SQL, is it possible to access the columns in that RECORD > object without knowing the column names? I.e. Can I grab whatever > value is the 3rd column? > > random_colname = 'foobar'; > sql = 'SELECT col1, col2, ' || random_colname || ' FROM table'; > > FOR mviews IN EXECUTE sql LOOP >--possible to access 3rd column of mviews? something like mviews[3]? > END LOOP; > > Regards, > Collin > > ---(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 -- Rick Albright Senior Quantitative Analyst Indie Research, LLC 254 Witherspoon Street Princeton, NJ 08542 (609)497-1030 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Joe Celko's method to "generate_series" not working?
Well, I am about a book and a-half away from reading all of the Joe Celko's books that I know of. I just came across an nice looking way to generate a series using a set oriented construct. However, I am not able to get it to work in PostgreSQL. The method follows from the SQL Programming Style p.164: SELECT hundred * 100 + ten * 10 + unit + 1 FROM ( VALUES( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9)) AS Units( unit ) CROSS JOIN ( VALUES( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9)) AS Tens( ten ) CROSS JOIN ( VALUES( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9)) AS Hundreds( hundred ); but I am getting: ?column? -- 1 (1 row) which isn't close to the 1000 rows that I am expecting. Is Mr. Celko mistaken? Does anyone know if PostgreSQL has any functions that I can turn these VALUES rows into columns so that the CROSS JOINS will work? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Joe Celko's method to "generate_series" not working?
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > Is Mr. Celko mistaken? Does anyone know if PostgreSQL has any functions that > I can turn these > VALUES rows into columns so that the CROSS JOINS will work? Having posted, I just realized what the correct syntax is: SELECT hundred * 100 + ten * 10 + unit + 1 FROM ( VALUES( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 ) ) AS Units( unit ) CROSS JOIN ( VALUES( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 ) ) AS Tens( ten ) CROSS JOIN ( VALUES( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 ) ) AS Hundreds( hundred ) ORDER BY hundred * 100 + ten * 10 + unit + 1 ; Sorry for the noise. :o( Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Needs Function
PLEASE GO TO FOLLOWING QUERY AND REVERT TO ME IF ANY QUERIES SELECT * FROM TEMP; DEPTNO EMPNO -- -- 10 B3091,B3092,B3093,B3085 11 3651,6521 12 H3062 1 SELECT DEPTNO,SUBSTR(EMPNO,1,5) EMPNO FROM TEMP WHERE DEPTNO=10 2 UNION 3 SELECT DEPTNO,SUBSTR(EMPNO,7,5) EMPNO FROM TEMP WHERE DEPTNO=10 4 UNION 5 SELECT DEPTNO,SUBSTR(EMPNO,13,5) EMPNO FROM TEMP WHERE DEPTNO=10 6 UNION 7 SELECT DEPTNO,SUBSTR(EMPNO,19,5) EMPNO FROM TEMP WHERE DEPTNO=10 8 UNION 9 SELECT DEPTNO,SUBSTR(EMPNO,1,4) EMPNO FROM TEMP WHERE DEPTNO=11 10 UNION 11 SELECT DEPTNO,SUBSTR(EMPNO,6,4) EMPNO FROM TEMP WHERE DEPTNO=11 12 UNION 13* SELECT DEPTNO,EMPNO FROM TEMP WHERE DEPTNO=12 SQL> / DEPTNO EMPNO -- -- 10 B3085 10 B3091 10 B3092 10 B3093 11 3651 11 6521 12 H3062 7 rows selected. 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. Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review at http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/Disclaimer.html internally within Tech Mahindra. 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.<>
[SQL] Needs Function
I need a user defined function for the following purpose
If I pass a string with comma ( , ) separated chars/values It should
be appear in next line...
Ex: select get_sep_str ('SK, rp, h, j, 6, 9, kl') from dual;
Output :
SK
rp
h
j
6
9
kl
(Or)
I have one table like this.
Temp Table:
Deptno number(10)
Empno varchar2(200);
Data in temp table:
Deptno Empno
---
10 B3091,B3092,B3093,B3085
11 3651,6521
12 H3062
Now, I want to display the data like this.
Deptno Empno
--
10 B3091
10 B3092
10 B3093
10 B3094
11 3651
11 6521
12 H3062
Now, how can I achieve this
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] Dynamic prepare possible in plpgsql?
"Collin Peters" <[EMAIL PROTECTED]> writes: > So if I have an INSERT inside a LOOP in a plpgsql function, it is only > prepared once? Once per session, yes (barring some special cases like polymorphic functions and trigger functions, which would get prepared once per session per calling situation) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
