Re: [despammed] [SQL] Balance Calculation
am 22.05.2005, um 12:15:56 +0800 mailte Muhyiddin A.M Hayat folgendes: Nice SQL ;-) > > everything is ok, but when record > 100 that query eat all my cpu > process and take a long time, i have wait for 3 mimutes but query I think, because for every record in your result is a full table scan required to calculate the balance. > > can you help me how to fix them. > Can I using function to fix them (how?) It is possible to create a extra table for the balance and a trigger for insert/update that calculates the balance for the transactions-table? I think, this should solve your perfomance-problem. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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
Re: [despammed] [SQL] Balance Calculation
am 22.05.2005, um 10:17:53 +0200 mailte Andreas Kretschmer folgendes: > > can you help me how to fix them. > > Can I using function to fix them (how?) > > It is possible to create a extra table for the balance and a trigger for > insert/update that calculates the balance for the transactions-table? > > > I think, this should solve your perfomance-problem. Sorry, this is not the solution for your problem, because this provide only the actual balance for a single account, but not the history. Mmh... A extra table with the oid and the balance for every row in transactions. Then you can do a index scan, but you need also a trigger that insert every transaction in this table and calculate the balance. Regards -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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: [despammed] [SQL] Balance Calculation
Do you tried to create an index on the account column ? I think it may help. Regards - Bernard LAMBEAU Département d'Ingénierie Informatique Université Catholique de Louvain-La-Neuve (Belgium) On 5/22/05, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > am 22.05.2005, um 10:17:53 +0200 mailte Andreas Kretschmer folgendes: > > > can you help me how to fix them. > > > Can I using function to fix them (how?) > > > > It is possible to create a extra table for the balance and a trigger for > > insert/update that calculates the balance for the transactions-table? > > > > > > I think, this should solve your perfomance-problem. > > Sorry, this is not the solution for your problem, because this provide > only the actual balance for a single account, but not the history. > > Mmh... > > A extra table with the oid and the balance for every row in > transactions. Then you can do a index scan, but you need also a trigger > that insert every transaction in this table and calculate the balance. > > > > > Regards > -- > Andreas Kretschmer(Kontakt: siehe Header) > Heynitz: 035242/47212, D1: 0160/7141639 > GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net > ===Schollglas Unternehmensgruppe=== > > ---(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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Calling Functions in RULEs
Hello, I would like to call multiple PL/pgSQL procedures (i.e. functions returning void) from INSERT, UPDATE and DELETE RULEs. Using "SELECT some_procedure();" is a possible way to do that, but it causes a dummy result table to be delivered to the application invoking the INSERT/UPDATE/DELETE. When using multiple SELECTs to call multiple functions inside the RULE, multiple result tables will be delivered to the application, which is very unhandy and causes trouble especially when using asynchronous command processing. Though "psql" does only show the last result table, all dummy result tables have to be fetched by PGgetResult(), when using asynchronous mode with libpq. Is there any tidy way to call a void-function inside a RULE without creating a result table that is passed to the application? Using a command like "UPDATE dummy_table SET dummy=1 WHERE some_procedure() NOTNULL;" would work, but that would be very dirty. Does a command like "PERFORM" (from PG/pgSQL) exist in SQL too, which discards the (void) result of the function call? Thanks for helping Jan Behrens || ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] datatype conversion on postgresql 7.4.1
hi everybody, i got the following problem: i have a database running on a postgresqlserver version 7.4.1, in the database is a table with 4 columns containing float-numbers. what i want to do now is, to convert the type of those columns from float to numeric, because i want to limit the positions after the decimal point to 2. what works fine in v. 8: either alter table fragment alter column x type numeric(15,2) or update fragment set x = round(x,2) but both wont work with version 7.4.1! when i look into the documentation it seems to say that in 7.4.1 round() can only round to a given number of positions after the decimal point when used with a numeric type, but not with floats! and if i try something like: alter table fragment alter column x type numeric(15,2) i get an syntax error, so this seems to be no feature in 7.4.1 (didnt find anything like that in the docs too) what can i do to solve this? there must be some way in postgresql 7.4.1, if there is no way to do that, i have to build up the whole database from scratchthere must be a way to convert float to numeric values in 7.4.1 , but i dont have a single clue... thx for any help in advance ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
