Re: [SQL] could not devise a query plan
Dear Tom, I didn't get your replies, but found them in the archives. Thanks a lot, the patched 7.4 works with the original query! (didn't patch 7.3 since it's a production db, and it works in 7.3 anyway) Thanks again, Yours, G. %--- cut here ---% \end - Original Message - From: "SZŰCS Gábor" <[EMAIL PROTECTED]> Sent: Tuesday, April 06, 2004 3:12 PM ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Utility of recursive queries?
Would recursive queries be the trick to doing things like unwinding a linked-list to either the head or tail, with: create table list ( id int primary key, parent int references list(id) ); insert into list values (1, null); -- head of chain in list insert into list values (2, 1); -- 1st child insert into list values (3, 2); -- second child Given a reference to id=3, would a recursive query be the trick to unrolling the list to discover id=1 as the head using a SQL one-liner? Is discovery possible in straight SQL w/o resorting to stored procedures (or modifying the table schema to directly point)? And, finally, would any potential recursive query implementation be noticably more efficient that a straightforward implementation in plpgsql, such as: create or replace function find_head(int) returns int as ' DECLARE cur_par INT; prev_par INT; BEGIN prev_par := $1; cur_par := parent from list where id = $1; WHILE cur_par is not null LOOP prev_par := cur_par; cur_par := parent from list where id = prev_par; END LOOP; return prev_par; END; ' language 'plpgsql'; James Robinson Socialserve.com ---(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] SQL challenge--top 10 for each key value?
Rod, > Something along the lines of the below would accomplish what you want > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of > SQL200N) Great leaping little gods! They added something called "row number" to the spec? Boy howdy, folks were right ... the ANSI committee really has completly blown off the relational model completely. First there was the addition of network-database functions so that IBM could make DB2 look more like a real database, now this When a standards committee becomes hostage to a handful of vendors, kiss real standards goodbye. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Utility of recursive queries?
James, > Would recursive queries be the trick to doing things like unwinding a > linked-list to either the head or tail, with: Yes. Also check out contrib/ltree and contrib/tablefunc in your handy-dandy PostgreSQL source code. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Utility of recursive queries?
James Robinson <[EMAIL PROTECTED]> writes: > Given a reference to id=3, would a recursive query be the trick to > unrolling the list to discover id=1 as the head using a SQL one-liner? I think you could do it, but don't have the syntax in my head. > would any potential recursive query implementation be > noticably more efficient that a straightforward implementation in > plpgsql Most likely not ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL challenge--top 10 for each key value?
Josh Berkus <[EMAIL PROTECTED]> writes: > Rod, > > > Something along the lines of the below would accomplish what you want > > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of > > SQL200N) > > Great leaping little gods! They added something called "row number" to the > spec? > > Boy howdy, folks were right ... the ANSI committee really has completly blown > off the relational model completely. If it's like Oracle's rownum then it's the row number of the *output*, not the position on disk. So it's not entirely blowing off the relational model any more than ORDER BY does. The weird thing is the number of cases where you want ORDER BY or rownum inside subselects. Which the solution to the original question needed. > When a standards committee becomes hostage to a handful of vendors, kiss > real standards goodbye. In the case of SQL was there ever any pretension otherwise? Was the SQL standard ever really useful as a "real standard"? I can write useful ANSI C89 code that will compile and work on any C compiler. Trying to write portable SQL92 code that does any useful work is about as productive as stapling bagels to your forehead. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Help with COPY command
I'm attempting to use the COPY command through the libpq C functions. In the psql command line editor, I'd have the following commands: COPY testtable FROM stdin WITH DELIMITER ','; 1, 2, 'a', 3, 4 5, 6, 'b', 7, 8 9, 10, 'c', 11, 12 \. My question is: Does this all have to be passed as one big command through libpq? For example, res = PQexec(conn, "COPY testtable FROM stdin WITH DELIMITER ',';\n", "1, 2, 'a', 3, 4\n5, 6, 'b', 7, 8\n9, 10, 'c', 11, 12\n\."); Or, can it be broken up into separate lines? For example, res = PQexec(conn, "COPY testtable FROM stdin WITH DELIMITER ',';"); res = PQexec(conn, "1, 2, 'a', 3, 4"); res = PQexec(conn, "5, 6, 'b', 7, 8"); res = PQexec(conn, "9, 10, 'c', 11, 12"); res = PQexec(conn, "\."); -Tony ---(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] Unknown Exception (piaozhenyu@163.com)
?? - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, April 07, 2004 12:46 PM Subject: Unknown Exception ([EMAIL PROTECTED]) > > Mail Transaction Failed - This mail couldn't be converted > > - failed message - > d9H_,ac,IAdU'Qha-e'Yp!u2M#Ur)tvT%-F((A-W98bGl > kJ? 3;ä8pi7.<78+ 9+m:1SjNob_NITr)ü?Fl5ßC1e&v2zUK&;6XkföNü > > Message has been sent as a binary attachment. > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] setting a non-standard date format for the duration of a session
I understand I can use the function to_date(s,f) for converting a string into a date using a non-standard format. I'm wondering if there is a way to set a non-standard date format for the duration of a session, something like the "ALTER SESSION SET NLS_DATE_FORMAT='f'" Oracle statement. Thank you. Kind regards, Cris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Counting rows from two tables in one query
Hi. I have a (simplified) table layout like this: +-+ +-+ | sub_a | +--+ | sub_b | +-+ | main | +-+ | id | +--+ | id | | main_id | > | id | < | main_id | | ... | | ... | | ... | +-+ +--+ +-+ What I am trying to get is a list that shows how many records from 'sub_a' and 'sub_b' are referencing 'main': main_id | count_a | count_b -+-+- 1 | 2 | 1 2 | 12 | 1 3 | 7 | 3 [..] This query obviously does not do what I need, it gives me the product of count_a and count_b in both columns instead: select main.idas main_id, count(sub_a.*) as count_a, count(sub_b.*) as count_b from main, sub_a, sub_b where sub_a.main_id = main.id and sub_b.main_id = main.id group by main.id having count(sub_a.*) > 0 and count(sub_b.*) > 0 ; Is it possible to get a list like the one above with a single query? thanks, stefan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Crypt() encryption
Hello, We are planning to use Crypt() to encrypt the application level users' passwords. Our question is that which encryption method the Crypt() is using? (DES, or some other encryption algorithms?) Because we concern about the strenth of the Crypt(). Thanks for your input. Christina ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help with COPY command
Ok. I found the libpq syntax for COPY in the Programmer's manual. I've
got a working version, but wanted to verify something.
PQexec(conn, "COPY foo FROM STDIN");
PQputline(conn, "3\thello world\t4.5\n");
PQputline(conn,"4\tgoodbye world\t7.11\n");
...
PQputline(conn,"\\.\n");
PQendcopy(conn);
1. I'm assuming that I can put in as many PQputline statements as I
want to between the PQexec("COPY ... FROM ...") and the terminator
line. Is that correct? No limit?
2. Do any of these lines need to be followed by a PQclear(res)? What
about the first PQexec?
-Tony
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pgsql multi-database queries
Dear Sir/Miss, Is there a way to do a multi-database select query??.. I fail to find the related solution... currently i i created seperate table call stockmaster under seperate database for each seperate organizations... When i want to queries out the stock item qty for multi database, i face a problem where sql only can work for single database.. How can i use the same SQL statement in Pgsql??.. "SELECT * FROM A.stockmaster,B.stockmaster where A.item=B.item" Regards, Ching _ Are you in love? Find a date on MSN Personals http://match.msn.com.my/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] hi sir urgent..required a Query
Hi Sir, I am attaching a document with this file showing two table.. <> It consist of two table VALUE AND VALUE_TYPE.. when i have value_type_id=1 i must get Value_varchar column value when i have value_type_id=2 i must get Value_int column value Please send a SQL query for this... I want to retrive Thanks and Regards Rajeev Chaudhary HCL Technologies Ltd, A-11, Sector 16, Noida, (U.P.) India Email : [EMAIL PROTECTED] Phone: +91 (120) 2510701 extn :3321 Disclaimer: This message and any attachment(s) contained here are information that is confidential,proprietary to HCL Technologies and its customers, privileged or otherwise protected by law.The information is solely intended for the individual or the entity it is addressed to. If you are not the intended recipient of this message, you are not authorized to read, forward, print,retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer. table_for_ you.doc Description: MS-Word document ---(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] Crypt() encryption
Christina Zhang wrote: > Hello, > > We are planning to use Crypt() to encrypt the application level > users' passwords. Our question is that which encryption method > the Crypt() is using? (DES, or some other encryption algorithms?) > Because we concern about the strenth of the Crypt(). Use MD5. That is the best method for this. There is an md5() function already in the database, and there is /contrib/pgcrypto. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Counting rows from two tables in one query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > What I am trying to get is a list that shows how many records from > 'sub_a' and 'sub_b' are referencing 'main': SELECT id, (SELECT COUNT(*) FROM sub_a WHERE sub_a.main_id=main.id), (SELECT COUNT(*) FROM sub_b WHERE sub_b.main_id=main.id) FROM main ORDER BY id; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200404092128 -BEGIN PGP SIGNATURE- iD8DBQFAd03pvJuQZxSWSsgRAuDuAJ0elm2bPjgC1bGPHnrotzXrPKCt4ACdFytf BglMm6IouFFZt1c19zST5ac= =7DbY -END PGP SIGNATURE- ---(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] SQL challenge--top 10 for each key value?
On Fri, 2004-04-09 at 18:43, Greg Stark wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > > Rod, > > > > > Something along the lines of the below would accomplish what you want > > > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of > > > SQL200N) > > > > Great leaping little gods! They added something called "row number" to the > > spec? > > > > Boy howdy, folks were right ... the ANSI committee really has completly blown > > off the relational model completely. > > If it's like Oracle's rownum then it's the row number of the *output*, not the > position on disk. So it's not entirely blowing off the relational model any > more than ORDER BY does. > > The weird thing is the number of cases where you want ORDER BY or rownum > inside subselects. Which the solution to the original question needed. It's not really like Oracles row num at all, though I suppose you can emulate rownum using it. The intention is that you will use it for "aggregates" like running totals, moving averages, counting, etc. http://www.devx.com/getHelpOn/10MinuteSolution/16573/1954?pf=true ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pgsql multi-database queries
On Thu, 8 Apr 2004, yek ching wrote: > Dear Sir/Miss, > > Is there a way to do a multi-database select query??.. I fail to find the > related solution... currently i i created seperate table call stockmaster > under seperate database for each seperate organizations... When i want to > queries out the stock item qty for multi database, i face a problem where > sql only can work for single database.. > > How can i use the same SQL statement in Pgsql??.. > "SELECT * FROM A.stockmaster,B.stockmaster where A.item=B.item" Look in the contrib/dblink directory in the source tar ball. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
