Re: [SQL] Problem using Subselect results
On Wed, Jul 23, 2003 at 14:51:48 +0200, [EMAIL PROTECTED] wrote: > I want to use the result of a subselect as condition of another one. The two selects you use ar both from items at the same level and hence can't reference one another. In your example below you could just use a join. > CREATE VIEW my_view AS SELECT b,c > (SELECT a, b FROM table2 WHERE b=1) my_ab, > (SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c; Something like: CREATE VIEW my_view AS SELECT table2.b, table3.c from table2, table3 where table2.b = 1 and table2.a = table3.a; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Very strange 'now' behaviour in nested triggers.
Dmitry Tkach <[EMAIL PROTECTED]> writes: > Why not get rid of 'now' alltogether? Are there any cases when it is > actually useful as opposed to now()? Data entry. You don't necessarily have the option to invoke a function, as opposed to just sending a string for the datetime input parser. (It would be rather difficult for an application to allow this one case without permitting SQL-injection attacks, I'd think.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Problem using Subselect results
Oliver, > CREATE VIEW my_view AS SELECT b,c > (SELECT a, b FROM table2 WHERE b=1) my_ab, > (SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c; This isn't possible in PostgreSQL, and I'm not sure it's possible anywhere. HOWEVER, if you put your subselects in the FROM clause instead, like so: CREATE VIEW my_sub AS SELECT my_ab.a, my_ab.b, my_c.c FROM (SELECT a, b FROM table2 WHERE b=1) my_ab, (SELECT a,c FROM table3, my_ab) my_c WHERE my_ab.a = my_c.a; OR you can mix-and-match subselect types: CREATE VIEW my_sub AS SELECT my_ab.a, my_ab.b, (SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c FROM (SELECT a, b FROM table2 WHERE b=1) my_ab; Although in the simplistic examples above there's not much reason to use a subselect at all, of course. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Very strange 'now' behaviour in nested triggers.
Tom Lane wrote: I put up a proposal in pgsql-hackers to change this behavior: http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php If we made that change then the "wrong" way of defining the default would fail in an obvious fashion --- the 'now' would get reduced to a particular time immediately at CREATE TABLE. Doubtless this would annoy some people, but the "right" way of defining the default isn't really any harder, and it would save folks from getting burnt in corner cases, like you were. Any comments? Why not get rid of 'now' alltogether? Are there any cases when it is actually useful as opposed to now()? Dima ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Very strange 'now' behaviour in nested triggers.
Tom Lane wrote: Dmitry Tkach <[EMAIL PROTECTED]> writes: Does it mean that the *application* (not the database) user would then have to know the exact specific way to represent the current time in his data entry form? Such an application looks like (how do I say it politely?) not a very user-friendly one to me :-) So? "now()" is certainly not more user-friendly than "now". Nope... it isn't. My point was that, if the app wanted to be user friendly, it would not attempt to take the input directly from user and stuff it into the sql - it would probably have some checkbox or drop-down list in the GUI form, that would indicate that the user wants the current time stamp, and use the the proper internal represntation in the generated sql... In that case having to execute a function (now()) would not make it vulnerable to a sql injection... My point is that wherever you are making the decision that you want to input current time, there may be layers between you and the database that will only want to pass data-value strings and not function invocations. Yeah... I've actually found one after I sent that last message :-) - it does seem to come handy in COPY TABLE - although, in that case, I'd say it would be much more useful to make COPY TABLE understand the defined defaults on the table, just the way INSERT does ... Dima ---(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] Very strange 'now' behaviour in nested triggers.
Dmitry Tkach <[EMAIL PROTECTED]> writes: > Does it mean that the *application* (not the database) user would then > have to know the exact specific way to represent the current time in his > data entry form? > Such an application looks like (how do I say it politely?) not a very > user-friendly one to me :-) So? "now()" is certainly not more user-friendly than "now". My point is that wherever you are making the decision that you want to input current time, there may be layers between you and the database that will only want to pass data-value strings and not function invocations. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Very strange 'now' behaviour in nested triggers.
Data entry. You don't necessarily have the option to invoke a function, as opposed to just sending a string for the datetime input parser. (It would be rather difficult for an application to allow this one case without permitting SQL-injection attacks, I'd think.) Does it mean that the *application* (not the database) user would then have to know the exact specific way to represent the current time in his data entry form? Such an application looks like (how do I say it politely?) not a very user-friendly one to me :-) Dima ---(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] NEW and a subselect in a rule
So, I met such a problem: it's impossible to use NEW in a subselect used in a (non-select) rule. The error is: . Is this a way to do that newertheless (without using of a function, of course)? Thanks in advance. ---(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] How can I to solute this problem?
I use tomcat+linux_postgresql+jsp to develop system. I start postgresql with 1024 processes. FE: postmaster -i -S -N 1024 -B 2048 -D /var/lib/pgsql/data My jsp doesn't implement connection pool.It directly connects postgresql by jdbc. After I run the Ui some times, the UI(jsp) would report "ieSorry,too many clientslg" .The exception is SQLException. I must restart tomcat or postgresql I can continue to running my UI. May this problem be soluted? Best regards. leon 2003-07-29
