[SQL] Why their is a limit in Postgresql (psql) Parameters..?
Hi All, I'm using Postgresql v7.3.3. I have a small question ... Why is that there is a maximum limit of 32 input parameters to thePostgresql function? Whereas stored procedures in Oracle and SQL Servertake more than 32 input arguments. So this puts extra burden on themiddleware developer to handle this stiuation at the time of migratingexisting databases in SQL Server or Oracle to Postgresql. Any Info/Suggestions will be highly appreciated. Are you Unmarried? Register in India's No 1 Matrimony
Re: [SQL] How to write this query!
On Sunday 13 July 2003 17:32, Jo wrote: > These are my PostgreSQL tables: > > pid | name > 1 | A > 2 | B > 3 | C > 4 | D > 5 | E > > tid | pid 1 | pid 2 | pid 3 > 1 | 1| 2| 3 > > Bascially, I would like to write a query to list only > the names which their "pid" match those pids in the > other table. If anyone knows, pls help!! Jo - not sure how your message took this long to reach the list, but it doesn't look like someone has answered, so... SELECT DISTINCT a.name FROM table_a AS a, table_b as b WHERE a.pid=b.pid1 OR a.pid=b.pid2 OR a.pid=b.pid3; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Why their is a limit in Postgresql (psql) Parameters..?
On Monday 21 July 2003 11:29, vijaykumar M wrote: > Hi All, > > I'm using Postgresql v7.3.3. I have a small question ... > > Why is that there is a maximum limit of 32 input parameters to the > Postgresql function? > Whereas stored procedures in Oracle and SQL Server > take more than 32 input arguments. So this puts extra burden on the > middleware developer to handle this stiuation at the time of migrating > existing databases in SQL Server or Oracle to Postgresql. It used to be 16 and was increased to 32 fairly recently (hmm - 7.3.0 according to the release notes). People used to tweak and recompile the source to increase from 16, so I suppose you could still do the same. Have a look in the mailing list archives, I seem to remember messages on this in the past. The reason why it's not bigger is that there hasn't been enough people saying "I need more parameters". -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] query or function
I need to create a set for use in a query that includes all dates between a start and ending date. Is this something I can do with a simple sql statement or do I need to make a function for this? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Why their is a limit in Postgresql (psql) Parameters..?
> The reason why it's not bigger is that there hasn't been enough people saying > "I need more parameters". That and a general speed penalty to all users of all functions. Make (whatever) the limitation is affect only those using a large number of parameters and you will find the limit set to a fairly high number. signature.asc Description: This is a digitally signed message part
[SQL] Postgresql Temporary table scripts..
Hi, I'm using Postgresqlv7.3.3. Actually my requirement was to create one temporary table and insert some values on it and finally return the inserted values. For this simple thing i'm struggling a lot with two errors. one is 'RELATION '' ALREADY EXISTS' -- This is happening when ever i called the function more than ones in the same connection. To avoid this, i had created a nested function, In inner function i had created the temporary table and inserted some values and finally i called the return values on outter fucntion. this time i get the error as 'RELATION 'x' DOES NOT EXIST'. I hope u all understood my problem.. if any of u send some sample example to work around this problem will be highly appreciated. Thanks in advance, Vijay Watch Hallmark. Enjoy cool movies. Win hot prizes!
Re: [SQL] avoid select expens_expr(col) like unneccessary calculations
Stephan Szabo wrote: > On 8 Jul 2003, Markus Bertheau wrote: > > > when you have > > select expensive_expression(column), * from table offset 20 limit 40 > > > > can you somehow save the cost for the first 20 calculations of > > expensive_expression? > > Right now the only way I can think of that might work is to push the > offset/limit into a subselect on table and then do the > expensive_expression at the top level. Well, you can do: SELECT * FROM (SELECT * FROM pg_class) AS pg_class so you could do: SELECT func(relname), * FROM (SELECT * FROM pg_class OFFSET 20 LIMIT 40) AS pg_class -- 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 4: Don't 'kill -9' the postmaster
Re: [SQL] min() and NaN
If a compare with NaN is always false, how about rewriting it as: result = ((arg1 < arg2) ? arg2 : arg1). Or better yet, swap arg1 and arg2 when calling float8smaller. Use flaost8smaller( current_min, value). JLL Tom Lane wrote: > > "Michael S. Tibbetts" <[EMAIL PROTECTED]> writes: > > I'd expect the aggregate function min() to return the minimum, valid > > numeric value. Instead, it seems to return the minimum value from the > > subset of rows following the 'NaN'. > > Not real surprising given than min() is implemented with float8smaller, > which does this: > > result = ((arg1 > arg2) ? arg1 : arg2); > > In most C implementations, any comparison involving a NaN will return > "false". So when we hit the NaN, we have arg1 = min so far, arg2 = NaN, > comparison yields false, result is NaN. On the next row, we have > arg1 = NaN, arg2 = next value, comparison yields false, result is next > value; and away it goes. > > We could probably make it work the way you want with explicit tests for > NaN in float8smaller, arranged to make sure that the result is not NaN > unless both inputs are NaN. But I'm not entirely convinced that we > should make it work like that. The other float8 comparison operators > are designed to treat NaN as larger than every other float8 value (so > that it has a well-defined position when sorting), and I'm inclined to > think that float8smaller and float8larger probably should behave > likewise. (That actually is the same as what you want for MIN(), but > not for MAX() ...) > > Comments anyone? > > 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] min() and NaN
Jean-Luc Lachance <[EMAIL PROTECTED]> writes: > If a compare with NaN is always false, how about rewriting it as: > result = ((arg1 < arg2) ? arg2 : arg1). That just changes the failure mode. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How access to array component
Joe
Tried, but...
>> select (foo(10::int2,20::int2))[1];
>> ERROR: parser: parse error at or near "[" at character 32
I'm using the version 7.3.3
Thanks..
---
> Cristian Cappo wrote:
> > >>> select __function(10::int2, 20::int2)[1]
> > ^^^ parsing error.
> >
>
> Try:
>
> create or replace function foo(int2, int2 ) returns _varchar as '
> select ''{1,2}''::_varchar
> ' language 'sql';
>
> regression=# select (foo(10::int2, 20::int2))[1];
> foo
> -
> 1
> (1 row)
>
> HTH,
>
> Joe
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Cristian Cappo Araujo
Desarrollo de Proyectos - Direccion Tecnica
Centro Nacional de Computación
Universidad Nacional de Asunción
email: [EMAIL PROTECTED]
tel. : 595-021-585550
fax : 595-021-585619
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] OR vs UNION
Gavin reported UNION faster than OR in some case when doing fts queries two years ago at O'Reilly. --- [EMAIL PROTECTED] wrote: > Actually, I have used a UNION to replace OR's, the case (simpliefied to) > something like this: > > Sample 1: > WHERE (f1 = 'v1' OR f1 = '') > AND (f2 = 'v2' OR f2 = '') > > Changed to Sample 2: > WHERE (f1 = 'v1') > AND (f2 = 'v2') > UNION > WHERE (f1 = 'v1') > AND (f2 = '') > UNION > WHERE (f1 = '') > AND (f2 = '') > > > Note that Sample 1 is actually a simplified version, the queries are not > exactly equivalent. > > The point is that sample 2 ran MUCH faster because: > a) The table was *very* large > b) The OR clauses of sample 1 prevented the use of an INDEX, > > Reason: It is faster to scan an index 3 times then scan this very large > table once. > > I do not know if there is a proof to say that one can *always* replace OR's > with a union, but sometimes certainly, and in this case it made things much > better... > > Terry Fielder > Manager Software Development and Deployment > Great Gulf Homes / Ashton Woods Homes > [EMAIL PROTECTED] > Fax: (416) 441-9085 > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] Behalf Of Josh Berkus > > Sent: Thursday, July 17, 2003 3:00 PM > > To: Scott Cain; [EMAIL PROTECTED] > > Subject: Re: [SQL] OR vs UNION > > > > > > Scott, > > > > > I have a query that uses a series of ORs and I have heard > > that sometimes > > > this type of query can be rewritten to use UNION instead and be more > > > efficient. > > > > I'd be interested to know where you heard that; as far as I > > know, it could > > only apply to conditional left outer joins. > > > > > select distinct > > f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id > > > from feature f, featureloc fl > > > where > > >(f.type_id = 219 OR > > > f.type_id = 368 OR > > > f.type_id = 514 OR > > > f.type_id = 475 OR > > > f.type_id = 426 OR > > > f.type_id = 456 OR > > > f.type_id = 461 OR > > > f.type_id = 553 OR > > > f.type_id = 89) and > > > fl.srcfeature_id = 1 and > > > f.feature_id = fl.feature_id and > > > fl.fmin <= 2491413 and fl.fmax >= 2485521 > > > > Certainly a query of the above form would not benefit from > > being a union. > > > > For readability, you could use an IN() statement rather than > > a bunch of ORs > > ... this would not help performance, but would make your > > query easier to > > type/read. > > > > -- > > -Josh Berkus > > Aglio Database Solutions > > San Francisco > > > > > > ---(end of > > broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- 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 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] min() and NaN
Is this a TODO? --- Tom Lane wrote: > Jean-Luc Lachance <[EMAIL PROTECTED]> writes: > > If a compare with NaN is always false, how about rewriting it as: > > result = ((arg1 < arg2) ? arg2 : arg1). > > That just changes the failure mode. > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- 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 4: Don't 'kill -9' the postmaster
Re: [SQL] How access to array component
Cristian Cappo A. wrote: Tried, but... >> select (foo(10::int2,20::int2))[1]; >> ERROR: parser: parse error at or near "[" at character 32 I'm using the version 7.3.3 Sorry, it works on 7.4devel, so I thought it might on 7.3 as well. In any case, this works on 7.3.3: test=# select f1[1] from (select foo(10::int2, 20::int2) as f1) as ss; f1 1 (1 row) HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] "Truncate [ Table ] name [Cascade]"?
Even better for quickly reinitialising all tables for unit-tests might be a command that does a "TRUNCATE ALL". JM2Cs, Wolfgang ---(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] min() and NaN
Bruce Momjian <[EMAIL PROTECTED]> writes: > Is this a TODO? It'll only take ten minutes to make it a DONE, once we figure out what the behavior ought to be. So far I think both Stephan and I argued that MIN/MAX ought to treat NaN as larger than all ordinary values, for consistency with the comparison operators. That was not the behavior Michael wanted, but I don't see that we have much choice given the wording of the SQL spec. Does anyone want to argue against that definition? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
