[SQL] Numbers
Hi list, Does anybody have numbers of PostgreSQL in action ? Numbers like the biggest insert in mileseconds, the larger database etc ? Regards... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Numbers
Στις Παρασκευή 24 Νοέμβριος 2006 15:07, ο/η Ezequias Rodrigues da Rocha έγραψε: > Hi list, > > Does anybody have numbers of PostgreSQL in action ? > > Numbers like the biggest insert in mileseconds, what do you mean here? LOCK TABLE in ACCESS EXCLUSIVE MODE; and the insert will take forever. > the larger database etc ? > > > Regards... -- Achilleas Mantzios ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Numbers
On Fri, 2006-11-24 at 11:07 -0200, Ezequias Rodrigues da Rocha wrote: > Does anybody have numbers of PostgreSQL in action ? > > Numbers like the biggest insert in mileseconds, the larger database etc ? First, you may want to post this in the GENERAL or in the PERFORMANCE lists since this isn't really about SQL. Second, you may want to look at the case studies page: http://www.postgresql.org/about/casestudies/. Third, the companies like EnterpriseDB and Pervasive may have some of what you're looking for since they have to measure themselves against the competition. And remember to take any numbers with a large grain of salt, YMMV, etc. Joe ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] select into
On Thursday 23 November 2006 08:45 pm, Tom Lane wrote: > Mulham freshcode <[EMAIL PROTECTED]> writes: > > execute sql_str1 into svc_data_rec ; > > > > svc_data_rec is a RECORD, which is supposed to be dynamic. > > This should work --- in PG 8.1 or later. In older versions you'd have > to fool around with a FOR ... IN EXECUTE ... loop. > > regards, tom lane The documentation for pl/pgsql in 8.1 and higher says different. http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN " SELECT INTO is not currently supported within EXECUTE." The change is reflected in the HISTORY text though. How is the best way to get a change made to the documentation? -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] select into
Adrian Klaver <[EMAIL PROTECTED]> schrieb: > On Thursday 23 November 2006 08:45 pm, Tom Lane wrote: > > Mulham freshcode <[EMAIL PROTECTED]> writes: > > > execute sql_str1 into svc_data_rec ; > > > > > > svc_data_rec is a RECORD, which is supposed to be dynamic. > > > > This should work --- in PG 8.1 or later. In older versions you'd have > > to fool around with a FOR ... IN EXECUTE ... loop. > > > > regards, tom lane > The documentation for pl/pgsql in 8.1 and higher says different. > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > " SELECT INTO is not currently supported within EXECUTE." Thats right. The solution with 8.1 or later is: EXECUTE INTO var, and _not_ SELECT INTO var. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] PgAgent [where is it?]
Hi lists, I need to create some jobs on my database but I can't find out the pgagent (executable). Can someone help me in this challenger ? I am using : PostgreSQL 8.1.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) Regards ... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] select into
On Friday 24 November 2006 06:55 am, Andreas Kretschmer wrote: > Adrian Klaver <[EMAIL PROTECTED]> schrieb: > > On Thursday 23 November 2006 08:45 pm, Tom Lane wrote: > > > Mulham freshcode <[EMAIL PROTECTED]> writes: > > > > execute sql_str1 into svc_data_rec ; > > > > > > > > svc_data_rec is a RECORD, which is supposed to be dynamic. > > > > > > This should work --- in PG 8.1 or later. In older versions you'd have > > > to fool around with a FOR ... IN EXECUTE ... loop. > > > > > > regards, tom lane > > > > The documentation for pl/pgsql in 8.1 and higher says different. > > > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PL > >PGSQL-STATEMENTS-EXECUTING-DYN " SELECT INTO is not currently supported > > within EXECUTE." > > Thats right. The solution with 8.1 or later is: > > EXECUTE INTO var, and _not_ SELECT INTO var. > > > Andreas Thanks for the information. Somewhere along the line I missed the distinction. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] select into
On Thursday 23 November 2006 10:54 pm, Mulham freshcode wrote: > Hi Tom, > > Thanks for the help. Am using version 8.0 and it seems like RECORD is not > that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP and it > does the trick. But am still finding it hard to move forward with this. I > have the name of table field in a varchar variable that i got from > information_schema.columns and I have the records variable that stores the > contains the data from that table. Usually I'd do something like > data_rec.col_name to extract the data from the record but now I don't know > the name per se. how can i say something like data_rec[col_name] where > col_name is a variable that has the actual column name. I found no examples > in the docs that explain this. Can it be done in version 8.0.1? > > I find variable substitution kind of confusing. I mean why is there no way > of saying explicitly replace this variable with its content before > executing the statement? > > Sorry for the long question, > and thanks again for the help > > Mustafa... > > Tom Lane <[EMAIL PROTECTED]> wrote: Mulham freshcode writes: > > execute sql_str1 into svc_data_rec ; > > > > svc_data_rec is a RECORD, which is supposed to be dynamic. > > This should work --- in PG 8.1 or later. In older versions you'd have > to fool around with a FOR ... IN EXECUTE ... loop. > >regards, tom lane > I am trying to sort this out. Are you trying to find the data for a single field from each table, or for some set of fields?. If you are looking for data from a single field couldn't you dispense with the RECORD variable and just build a query of the form SELECT col_name FROM tbl_name. If you want to go through a set of fields then it would involve some nested loops. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: 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] select into
Adrian Klaver <[EMAIL PROTECTED]> writes: > On Thursday 23 November 2006 08:45 pm, Tom Lane wrote: >> This should work --- in PG 8.1 or later. > The documentation for pl/pgsql in 8.1 and higher says different. > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > " SELECT INTO is not currently supported within EXECUTE." That says you can't do EXECUTE 'select ... into foo ...'; which is not the same thing as EXECUTE 'select ...' into foo; regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] select into
Hi Adrian, I have number of similar tables that have different number of fields (similar in functionality). An in my stored procedure am trying to select a row from one of these tables (that i don't know in advance, hence the use of record) and return the data in the form of a table that has column_name:value pairs. where column name is that from the original table. I have no problem finding the column names but I don't know how to say data[column_name] to get the corresponding value. Is there a way to do it in pgsql? here is my code so far sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ; for svc_data_rec in execute sql_str1 loop end loop; -- get service_user table's column names for col_name in select column_name from information_schema.columns where table_name~svc_tbl_name loop raise notice 'Column name:%', col_name.column_name; raise notice 'Value: %', svc_data_rec[col_name.column_name]; end loop; Thank you, Mustafa ... Adrian Klaver <[EMAIL PROTECTED]> wrote: On Thursday 23 November 2006 10:54 pm, Mulham freshcode wrote: > Hi Tom, > > Thanks for the help. Am using version 8.0 and it seems like RECORD is not > that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP and it > does the trick. But am still finding it hard to move forward with this. I > have the name of table field in a varchar variable that i got from > information_schema.columns and I have the records variable that stores the > contains the data from that table. Usually I'd do something like > data_rec.col_name to extract the data from the record but now I don't know > the name per se. how can i say something like data_rec[col_name] where > col_name is a variable that has the actual column name. I found no examples > in the docs that explain this. Can it be done in version 8.0.1? > > I find variable substitution kind of confusing. I mean why is there no way > of saying explicitly replace this variable with its content before > executing the statement? > > Sorry for the long question, > and thanks again for the help > > Mustafa... > > Tom Lane wrote: Mulham freshcode writes: > > execute sql_str1 into svc_data_rec ; > > > > svc_data_rec is a RECORD, which is supposed to be dynamic. > > This should work --- in PG 8.1 or later. In older versions you'd have > to fool around with a FOR ... IN EXECUTE ... loop. > >regards, tom lane > I am trying to sort this out. Are you trying to find the data for a single field from each table, or for some set of fields?. If you are looking for data from a single field couldn't you dispense with the RECORD variable and just build a query of the form SELECT col_name FROM tbl_name. If you want to go through a set of fields then it would involve some nested loops. -- Adrian Klaver [EMAIL PROTECTED] - Access over 1 million songs - Yahoo! Music Unlimited.
