Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
Phillip, > The main reason I use them is to find the 'next' or > 'previous' record in a > list (eg. next date, next ID). eg. > The fact that Dec RDB, Oracle and SQL/Server all allow it > probably means > that there is a reasonable user base out there who think > it's a good idea. Makes sense. Fortunately, in PGSQL there's another function to grab the next ID. As for dates ... hmmm... there we come up against the "no subselect in FROM" bug, don't we? I get the problem. (Personally, I've always SELECTed into a variable in MSSQL rather than using the syntac you're suggesting. I've found that the order that the MSSQL chooses to execute query segments in can cause some unpredicatble results ... ) -Josh Berkus
Re: [SQL] sql query not using indexes
Stephan Szabo <[EMAIL PROTECTED]> writes: >> Ok I agree with you on the real database there are 127,300 rows and there >> are certanly a great number of rows > 'AAA'. But, supose I make a query >> select * from table where code > 'AAA' limit 10. it will read the entire >> table only to give me the first 10 while in release 6.5 it will fetch the >> index for the first 10 in a very fast manner, indeed the 6.5 release >> resolves in 1 second while the 7.0 release resolves in 10-20 sec. > Hmm, I believe Tom Lane was doing alot of stuff with the optimizer and > limit but I don't remember if that was before or after the 7.0 release. > It might be worth trying on current sources to see if that goes back to > an index scan. No, it'll still do a seqscan. 6.5 was in fact too ready to use indexscans; the current code may have overcorrected a shade, but I think it's closer to reality than 6.5 was. As Hiroshi already commented, the difference in results suggests that the desired data is very nonuniformly scattered in the table. 7.0 computes cost estimates on the assumption that the target data is uniformly scattered. For a sufficiently nonselective WHERE condition (ie, one that the planner thinks will match a large fraction of the table's rows) it looks better to do a seqscan and pick up the matching rows than to follow the index pointers. Adding a LIMIT doesn't change this equation. I like Hiroshi's recommendation: add an ORDER BY to help favor the indexscan. regards, tom lane
Re: [SQL] sql query not using indexes
On Fri, 22 Sep 2000, Tom Lane wrote:
> indexscans; the current code may have overcorrected a shade, but I think
> it's closer to reality than 6.5 was.
>
> As Hiroshi already commented, the difference in results suggests that
> the desired data is very nonuniformly scattered in the table. 7.0
> computes cost estimates on the assumption that the target data is
> uniformly scattered. For a sufficiently nonselective WHERE condition
> (ie, one that the planner thinks will match a large fraction of the
> table's rows) it looks better to do a seqscan and pick up the matching
> rows than to follow the index pointers. Adding a LIMIT doesn't change
> this equation.
>
> I like Hiroshi's recommendation: add an ORDER BY to help favor the
> indexscan.
Yeah, I didn't notice the lack of the order by when I responded. I
forget that order by isn't required to use limit since it's fairly
ugly to not use one ("What, you wanted to get a implementation defined
effectively random 10 rows?")
[SQL] how to store a query, that results in a table
thank you jie Liang for your response, but my problems are: 1. How to store a query in the database. 2. How to give a parameter from outside of database, for example: select * from table1 where row1 = my_parameter_outside. Give me an example please. begin:vcard n:brito;nelson x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;28480 fn:nelson brito end:vcard
Re: [SQL] how to store a query, that results in a table
Hi, So, your question is not client side, you want store a procedure in db(server side) can accept para from client side. I have not seen Pg has a functionality to do like store procedure or package like Oracle ( maybe I don't know), so , I suggest that : 1 use embeded SQL 2. store query as a SQL in shell script( because shell script can accept parameters) in shell , you can say: echo "select * from table1 where row1=$1"|rsh pg_server /usr/local/pgsql/bin/psql -U robt db (e.g . remote shell call, local is same) 3. if you use bash shell, you can also use pgbash(http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html) Nelson wrote: thank you jie Liang for your response, but my problems are: 1. How to store a query in the database. 2. How to give a parameter from outside of database, for example: select * from table1 where row1 = my_parameter_outside. Give me an example please. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] how to store a query, that results in a table
This is not really possible with postgresql at the moment. Better off trying to work around, perhaps using a view. That way you have a way to change the select statement without actually modifying your client code. Keith. At 06:09 PM 22/09/2000 -0400, Nelson wrote: >thank you jie Liang for your response, but my problems are: >1. How to store a query in the database. >2. How to give a parameter from outside of database, for example: >select * from table1 where row1 = my_parameter_outside. >Give me an example please. > >
[SQL] how to store a query, that results in a table
hi. I have a problem. I require to store a query sql in postgresql-7.0 like: example. select * from table1 where row1(table1)=parameter1 If i execute this query directly, I don't have problem. I want to store this query in order to execute from a client program (visual basic 6.0), but i don't know how to? I tried to store the query like a function (create function ...), but it was impossible to find a way in order to obtain a table like result. Only we could obtain a single row by using the rtype " returns setof varchar". I hope a soon answer. Thank you. Nelson B.
Re: [SQL] how to store a query, that results in a table
Hi, there,
If the client machine is a trusted machine in your company, use remote
shell 'rsh' can
call a script of SQL.
If not , you had better use embeded SQL.
I don't know how visual basic embed SQL, but I think is same way as
other language,
in postgres:
#db> create user robot with password 'wowwow';
$db> grant all on table1 to robot;
In perl , it's very similar like this:
use Pg;
my $conn;
sub connectDb {
$conn
= Pg::connectdb("dbname=db host=dbserver port=5432 user=robot password=wowwow");
if ( $conn->status
!= Pg::PGRES_CONNECTION_OK ) {
die "Cant open postgres! : " . $conn->errorMessage . "\n";
}
return;
}
#main
connectDb();
my $query="select * from
table1 where row1=parameter1";
print $query,"\n";
my $res=$conn->exec($query);
$res->cmdStatus || die $conn->errorMessage.":$!\n";
my $rows= $res->ntuples;
for (my $k = 0 ; $k <
$rows; $k++){
my $field1=$res->getvalue($k,0);
my $field2=$res->getvalue($k,1);
print $field1,"\t",field2,"\n";
}
root wrote:
hi.
I have a problem.
I require to store a query sql in postgresql-7.0 like:
example.
select * from table1 where row1(table1)=parameter1
If i execute this query directly, I don't have problem.
I want to store this query in order to execute from a client program
(visual basic 6.0), but i don't know how to?
I tried to store the query like a function (create function ...), but
it
was impossible to find a way in order to obtain a table like result.
Only we could obtain a single row by using the rtype " returns setof
varchar".
I hope a soon answer.
Thank you.
Nelson B.
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
