Re: [GENERAL] Filling null values
On 08/05/2011 07:32 PM, jeffrey wrote: I have a table that looks like this: homeidcity date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles2/4/2005 938 pre 124 NULLNULL 266 pre 124 los angeles7/4/2006 777 post I'd like to write a query so that I get the following result: homeidcity date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932post 124 los angeles2/4/2005 938 pre 124 los angeles2/4/2005 266 pre 124 los angeles7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? Thanks, Jeff You need a primary key on the table to do this. Then you can do a self join and update the correct values. Sim
Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
Hello Fernando, I was sorry to read the harsh responses your request got here. The thing that has always appealed to me about the free software movement is the spirit of cooperation and mutual help that many involved exhibit. You quite rightly point out the hypocrisy of those who call someone a freeloader when they themselves use free software in profit making ventures without sharing their profits with the software's developers and contributors. Please be assured that not everyone here reacted negatively to your post. I wish you success in your search. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On Sun, Aug 7, 2011 at 1:40 AM, David Johnston pol...@yahoo.com wrote: On Aug 6, 2011, at 18:03, Stuart McGraw smcg2...@frii.com wrote: My point, while coming across a little harsh apparently, is that emphasis on requiring a free service projects a certain personality. If a good service is free, like PostgreSQL is, the emphasis of using it is justified if you do good things by it. The same is valide for free hardware, but as 'we' know for the hardware is not exactly the same. However, because free file hosting exists and because my funded project ended and I cannot continue to economically support it with other incomes, I tried to ask if the use of a free hosting platform where to install free software (java, PostgreSQL, etc.) could exist. Even just adding or low cost would have helped. Unfortunately, you cannot ask questions on my behalf. I usually ask questions on the basis of my requirements and knowledge. Then, if you want, you can answer with possible comments or not or star a discussion. And finally, I would thank you in any case for your attention and your time. That said, it wasn't the original request the got me to respond but the part about needing to eat. I have never talked about the needing to eat!! I just wrote that my project ended, but fortunately the life of a researcher/engineer is not made just of funded projects. In any case thanks for your concern. :-) I know we started it with the comment about why there are no free hosting providers and I am just as guilty for adding to it. In all, though, I didn't mean to say anyone IS a free-loader only that you can be perceived as one and such perceptions can suppress otherwise useful responses. In the end everyone free-loads and is taken advantage of at the same time in many different areas; and any judgements should be made only when many facts are known (if ever). Sorry if I criticized the free model as somebody has defined it and as probably it is. I explained my ideas in my previous email and I don't want to bore you repeating again my point of view. I apologize for my tone earlier but to be honest this is probably one of the calmest flame-wars I've ever seen :) I apologize too. :-) Peace done! The bottom line is I would not expect to find any individual or company willing or able to offer such a service, to the general public, for free. And it is a service you are requesting as opposed to a product like PostgreSQL. A product is more likely to be improved by the people using it compared to a service, and those improvements are likely to make it back into the original. But, there are a number of companies that do what you need for a price. If you feel what you are doing is important it should at least be worth your time to talk to these companies and see what arrangements can be made instead of dismissing them outright because they charge for their services. You may find someone inclined to take on pro-bono work for a good cause; especially if your needs are modest. In short, ask for everything and then perform the filtering yourself instead of asking others to filter for you - only you know what your actual situation is which makes anyone else's filtering only an uninformed guess. As answered before, you cannot ask questions on my behalf. :-) I already filtered any no free solutions for valid reasons. What you suggest would have caused just a great loss of time for me and for the hosting providers. David J. Hello Fernando, I was sorry to read the harsh responses your request got here. The thing that has always appealed to me about the free software movement is the spirit of cooperation and mutual help that many involved exhibit. You quite rightly point out the hypocrisy of those who call someone a freeloader when they themselves use free software in profit making ventures without sharing their profits with the software's developers and contributors. Please be assured that not everyone here reacted negatively to your post. I wish you success in your search. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
John, Craig, how do you explain the services of file hosting? By those services millions of persons free-load pictures, videos, text, GBs of data, etc.. I think that what I asked is quite similar, that is the use of a piece of remote hardware where to have free software installed. The difference in my opinion is in the fact that I implicitly asked also for the use of a free operating system, but not in the hardware or in its maintenance. On Sun, Aug 7, 2011 at 3:56 AM, John R Pierce pie...@hogranch.com wrote: On 08/06/11 4:40 PM, David Johnston wrote: The bottom line is I would not expect to find any individual or company willing or able to offer such a service, to the general public, for free. And it is a service you are requesting as opposed to a product like PostgreSQL. A product is more likely to be improved by the people using it compared to a service, and those improvements are likely to make it back into the original. indeed, especially a service like hosting that has significant ongoing hard costs involved... a colocated server requires power, air conditioning, network traffic and transit fees, management, physical security, and the cost of the hardware itself, which has typically a 3-5 year lifespan (in 3 years, newer hardware can do so much more work its often not cost effective to keep the old hardware online). On Sun, Aug 7, 2011 at 4:25 AM, Craig Ringer ring...@ringerc.id.au wrote: On 7/08/2011 1:08 AM, Scott Ribe wrote: After open source for the software, we will wait for open resource for the hardware (this is just a first example http://www.arduino.cc/, even if of different nature). While the plans may be free, the actual hardware sure as hell won't be. A bit OT, but Arduino is not so much a will as an is. It's in wide-spread use and has even been adopted for the base of the new Android peripheral development system - the Android Open Accessory Development Kit. http://developer.android.com/**guide/topics/usb/adk.htmlhttp://developer.android.com/guide/topics/usb/adk.html I struggle to see any connection between Arduino and PostgreSQL, though. They're very different kinds of free/open source, as software is its specification and can be distributed at no cost, but you can't just download a hardware device and use it. -- Craig Ringer
Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On 08/07/11 1:46 AM, Fernando Pianegiani wrote: how do you explain the services of file hosting? By those services millions of persons free-load pictures, videos, text, GBs of data, etc.. I think that what I asked is quite similar, that is the use of a piece of remote hardware where to have free software installed. The difference in my opinion is in the fact that I implicitly asked also for the use of a free operating system, but not in the hardware or in its maintenance. that stuff is usually advertising supported. many of those 'free' file hosting systems charge to let people download at reasonable speeds, and make the download process painful for freeloaders. or, like Google Picasa's image service, they charge if you use more than a couple gigabytes.how do you attach advertising to a user programmed tomcat server with a postgres database? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On Sun, Aug 7, 2011 at 11:22 AM, John R Pierce pie...@hogranch.com wrote: On 08/07/11 1:46 AM, Fernando Pianegiani wrote: how do you explain the services of file hosting? By those services millions of persons free-load pictures, videos, text, GBs of data, etc.. I think that what I asked is quite similar, that is the use of a piece of remote hardware where to have free software installed. The difference in my opinion is in the fact that I implicitly asked also for the use of a free operating system, but not in the hardware or in its maintenance. that stuff is usually advertising supported. many of those 'free' file hosting systems charge to let people download at reasonable speeds, and make the download process painful for freeloaders. or, like Google Picasa's image service, they charge if you use more than a couple gigabytes.how do you attach advertising to a user programmed tomcat server with a postgres database? This is an interesting question for people who want to develop business in the field of the cloud. -- john r pierceN 37, W 122 santa cruz ca mid-left coast
[GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On 6/08/2011 4:02 PM, Fernando Pianegiani wrote: Hello, do you know any FREE hosting platforms where PostgreSQL, Java SDK, Tomcat (or other web servers) can be already found installed or where they can be installed from scratch? About the only hope I know of is hub.org . http://archives.postgresql.org/pgsql-announce/2010-01/msg0.php They're offering one-year free VPS services at certain times of year. By the way, one of the reasons you're not finding much free hosting for PostgreSQL is that it takes a fair bit of work to run Pg multi-tenanted. Your additional requirement for Java and Tomcat means you're certain to be stuck with a virtual private server (VPS) or a BSD Jail based host. I'll be very surprised if you can find any offerings that are free (as opposed to free trial) in that vein, but I wish you luck in your search. What you *might* be able to do is find sponsorship for hosting or find someone who'll grant you free hosting for your project because they think that particular project is worthwhile and important. That'll depend a great deal on what you're trying to host and what the likely load will be. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On Sun, Aug 7, 2011 at 2:41 PM, Craig Ringer ring...@ringerc.id.au wrote: On 6/08/2011 4:02 PM, Fernando Pianegiani wrote: Hello, do you know any FREE hosting platforms where PostgreSQL, Java SDK, Tomcat (or other web servers) can be already found installed or where they can be installed from scratch? About the only hope I know of is hub.org . http://archives.postgresql.**org/pgsql-announce/2010-01/**msg0.phphttp://archives.postgresql.org/pgsql-announce/2010-01/msg0.php They're offering one-year free VPS services at certain times of year. Thanks a lot! By the way, one of the reasons you're not finding much free hosting for PostgreSQL is that it takes a fair bit of work to run Pg multi-tenanted. Your additional requirement for Java and Tomcat means you're certain to be stuck with a virtual private server (VPS) or a BSD Jail based host. I'll be very surprised if you can find any offerings that are free (as opposed to free trial) in that vein, but I wish you luck in your search. I see... What you *might* be able to do is find sponsorship for hosting or find someone who'll grant you free hosting for your project because they think that particular project is worthwhile and important. That'll depend a great deal on what you're trying to host and what the likely load will be. this is very difficult, but it is exactly what I am doing in environments different from this one. Even if this risks to be considered (not so positively) as a request of charity... :-) -- Craig Ringer
Re: [GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
this is very difficult, but it is exactly what I am doing in environments different from this one. Even if this risks to be considered (not so positively) as a request of charity... :-) At that point, unless you have confidentiality requirements, why not just tell everyone what it is you are working on and see if anyone responds favorably? It woul normally be deemed off-topic but at this point one more non-Postgresql post isn't going to make a big difference on this thread. Fundraising for a cause is quite a bit different than asking for a personal gift and it sound like your request falls into the former category. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On Sun, Aug 7, 2011 at 4:22 PM, David Johnston pol...@yahoo.com wrote: this is very difficult, but it is exactly what I am doing in environments different from this one. Even if this risks to be considered (not so positively) as a request of charity... :-) At that point, unless you have confidentiality requirements, why not just tell everyone what it is you are working on and see if anyone responds favorably? It woul normally be deemed off-topic but at this point one more non-Postgresql post isn't going to make a big difference on this thread. Fundraising for a cause is quite a bit different than asking for a personal gift and it sound like your request falls into the former category. Dear David, thank you for your post. I have not posted exactly a non-PostgreSQL post, in fact I asked for information about possible services of free hosting platforms with PostgreSQL installed. I repeat that I didn't ask for a hosting platform but for information about possible inherent free services. The item of research focuses on the remote detection of events of health hazard, like in particular the cardiac atrial fibrillation, by wireless sensors installed on the body of the patient and a phone that forwards the data towards the hosting. If somebody can be interested I pray him to ask me for more information writing just to my email address. Thanks a lot! David J.
[GENERAL] Pg multi-tenanted hard? was: FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On Sun, 07 Aug 2011 20:41:27 +0800 Craig Ringer ring...@ringerc.id.au wrote: By the way, one of the reasons you're not finding much free hosting for PostgreSQL is that it takes a fair bit of work to run Pg multi-tenanted. Your additional requirement for Java and Tomcat Why should pg be harder than anything else? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pg multi-tenanted hard? was: FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On 7/08/2011 11:43 PM, Ivan Sergio Borgonovo wrote: On Sun, 07 Aug 2011 20:41:27 +0800 Craig Ringerring...@ringerc.id.au wrote: By the way, one of the reasons you're not finding much free hosting for PostgreSQL is that it takes a fair bit of work to run Pg multi-tenanted. Your additional requirement for Java and Tomcat Why should pg be harder than anything else? Before I go on: I'm not running a hosting provider. Most of this is gathered from time on the lists, plus my own observations from using Pg in situations where I want to prevent different apps sharing a DB from affecting each other. Given that qualification: Things that contribute to making it harder to offer multi-tenanted database hosting services with Pg include: The global catalogs, in particular global user lists; relatively limited options for automatic load limitation and prioritisation; difficulty of preventing certain classes of effective denial of service by authorized users; shared WAL making it harder to isolate load and limit one DB's effects on another; difficulties completely hiding users from each other; the need to tune autovacuum to make sure it keeps up with load; etc. In Pg, DBs in a cluster affect each other in various ways, but because of the pinned-in-RAM nature of shared_buffers its hard to run one cluster per user as would be ideal. This doesn't mean you can't use Pg for multi-tenanted DB hosting. It just makes it harder, and means its more sysadmin work, therefore more time and cost. There are DBs - mostly $OMFGLOTS DBs - that offer much greater facilities for controlling access and isolating load than Pg does and are better suited to multi-tenanted use. Of course, their costs are such that it's probably cheaper to hire an army of sysadmins to run a giant cluster of Pg boxes instead... -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pg multi-tenanted hard? was: FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
On Mon, 08 Aug 2011 05:38:02 +0800 Craig Ringer ring...@ringerc.id.au wrote: On 7/08/2011 11:43 PM, Ivan Sergio Borgonovo wrote: On Sun, 07 Aug 2011 20:41:27 +0800 Craig Ringerring...@ringerc.id.au wrote: By the way, one of the reasons you're not finding much free hosting for PostgreSQL is that it takes a fair bit of work to run Pg multi-tenanted. Your additional requirement for Java and Tomcat Why should pg be harder than anything else? There are DBs - mostly $OMFGLOTS DBs - that offer much greater OK. I forgot to specify anything else in the same league or shortly open source. Nice reading anyway, thanks. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
Fernando, You just posted your question to multiple innappropriate mailing lists. Please do not do that again. --Josh Berkus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [TESTERS] FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.?
Josh, sorry for multiple posting. This is the description of this mailing list: General discussion area for users. Apart from compile, acceptance test, and bug problems, most new users will probably only be interested in this mailing list (unless they want to contribute to development or documentation). All non-bug related questions regarding PostgreSQL's version of SQL, and all installation related questions that do not involve bugs or failed compiles, should be restricted to this area. Please note that many of the developers monitor this area. So, in my opinion asking if somebody knows a hosting service where PostgreSQL can be used for free is not inappropriate. But if you consider it inappropriate and you are in a position to cancel my posts you have to cancel them without any hesitation asap. Fernando On Mon, Aug 8, 2011 at 12:45 AM, Joshua Berkus j...@agliodbs.com wrote: Fernando, You just posted your question to multiple innappropriate mailing lists. Please do not do that again. --Josh Berkus
[GENERAL] Effect of a kill -9 on postgres
Hi all, A few days ago one of our postgres (8.3.12) servers was a bit unhappy, and someone decided to try a kill -9 on a backend process after a kill (TERM) was ineffective. I've read many times in the past that a kill -9 can be pretty hazardous to a postgres' health, and now it seems I get to see first hand how hazardous it really is :( Fortunately postgres seems to have detected the -9 signal and brought the system down: 2011-08-05 17:17:53 EST redacted.com 10.3.0.3(39556) ad...@redacted.com WARNING: terminating connection because of crash of another server process 2011-08-05 17:17:53 EST redacted.com 10.3.0.3(39556) ad...@redacted.com DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2011-08-05 17:17:53 EST redacted.com 10.3.0.3(39556) ad...@redacted.com HINT: In a moment you should be able to reconnect to the database and repeat your command. After the barrage of those messages, there: 2011-08-05 17:17:54 ESTLOG: all server processes terminated; reinitializing 2011-08-05 17:17:55 ESTLOG: database system was interrupted; last known up at 2011-08-05 17:15:33 EST 2011-08-05 17:17:55 ESTLOG: database system was not properly shut down; automatic recovery in progress 2011-08-05 17:17:55 ESTLOG: redo starts at 208/5013A758 2011-08-05 17:17:55 ESTLOG: record with zero length at 208/51497498 2011-08-05 17:17:55 ESTLOG: redo done at 208/51497468 2011-08-05 17:17:55 ESTLOG: last completed transaction was at log time 2011-08-05 17:17:52.709539+10 2011-08-05 17:18:03 ESTLOG: autovacuum launcher started 2011-08-05 17:18:03 ESTLOG: database system is ready to accept connections For each of the other backend processes. I'm a bit worried about corruption and would like to know: - Is postgres 8.3.12 susceptible to corruption when a backend process is -9'd? - How do we confirm that there has been no corruption? We have nightly backups that dump every database in the cluster, and looking over postgres' logs I can't see any errors that might point to corruption... I guess that's a good sign - is there anything else I can look in to? Thanks very much, --Royce -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query with rightmost function does not use index
For a table where column col has an index, the query:
SELECT ... FROM ... WHERE col = MD5('')
doesn't seem to use an index but
SELECT ... FROM ... WHERE col = 'd41d8cd98f00b204e9800998ecf8427e'
does.
Is this a gotcha?
I'm using PostgreSQL 8.4.8 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real
(Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit
Re: [GENERAL] Effect of a kill -9 on postgres
The only time kill -9 should be a data corruption issue is if you kill the postMASTER (not just a backend) then remove the postmaster.pid file from the datadir and relaunch the postmaster without first making sure the worker backends are all shut down. You need to load the shotgun, aim it carefully at your foot, take the safety off and pull the trigger. It's not easy. A kill -9 shouldn't even cause problems if you're running on unsafe write cached storage or (afaik) with fsync=off. Though for other reasons you should never be doing either without streaming replication, good backups, and a willingness to life some data. On Aug 8, 2011 9:01 AM, Royce Ausburn royce...@inomial.com wrote: Hi all, A few days ago one of our postgres (8.3.12) servers was a bit unhappy, and someone decided to try a kill -9 on a backend process after a kill (TERM) was ineffective. I've read many times in the past that a kill -9 can be pretty hazardous to a postgres' health, and now it seems I get to see first hand how hazardous it really is :( Fortunately postgres seems to have detected the -9 signal and brought the system down: 2011-08-05 17:17:53 EST redacted.com 10.3.0.3(39556) admin@redacted.comWARNING: terminating connection because of crash of another server process 2011-08-05 17:17:53 EST redacted.com 10.3.0.3(39556) admin@redacted.comDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2011-08-05 17:17:53 EST redacted.com 10.3.0.3(39556) admin@redacted.comHINT: In a moment you should be able to reconnect to the database and repeat your command. After the barrage of those messages, there: 2011-08-05 17:17:54 EST LOG: all server processes terminated; reinitializing 2011-08-05 17:17:55 EST LOG: database system was interrupted; last known up at 2011-08-05 17:15:33 EST 2011-08-05 17:17:55 EST LOG: database system was not properly shut down; automatic recovery in progress 2011-08-05 17:17:55 EST LOG: redo starts at 208/5013A758 2011-08-05 17:17:55 EST LOG: record with zero length at 208/51497498 2011-08-05 17:17:55 EST LOG: redo done at 208/51497468 2011-08-05 17:17:55 EST LOG: last completed transaction was at log time 2011-08-05 17:17:52.709539+10 2011-08-05 17:18:03 EST LOG: autovacuum launcher started 2011-08-05 17:18:03 EST LOG: database system is ready to accept connections For each of the other backend processes. I'm a bit worried about corruption and would like to know: - Is postgres 8.3.12 susceptible to corruption when a backend process is -9'd? - How do we confirm that there has been no corruption? We have nightly backups that dump every database in the cluster, and looking over postgres' logs I can't see any errors that might point to corruption... I guess that's a good sign - is there anything else I can look in to? Thanks very much, --Royce -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] table / query as a prameter for PL/pgSQL function
Hi, It is possible to pass query result (or cursor?) as function parameter? I need a function which emits zero or more rows per input row (map function from mapreduce paradigm). Function returns record (or array): (value1, value2, value3) I've tried the following: 1) create or replace function test (r record) returns setof record as $$ ... Doesn't work: PL/pgSQL functions cannot accept type record 2) pass query as text parameter and open no scroll cursor inside the function It works but it's ugly. 3) hardcode the query inside function Similar to (2) and looks better but I need several functions with different queries inside: ... for r in (query) loop ... end loop; ... 4) use function in select clause: select my_map_func(col1, col2, col3, col4) from ... -- the rest of the query In this case I wasn't able figure out how to access record members returned by the function: select ?, ?, ?, count(*) from ( select my_map_func(col1, col2, col3, col4) as map_func_result from ... ) as map group by 1, 2, 3 The '?' should be something like map.map_func_result.value1 (both map.value1 and map_func_result.value1 doesn't not work). If function returns array then I can access value1 by using map_func_result[1] Is there a better way how to solve this? I'm kind of satisfied with 4 (maybe 3) but it is little bit cumbersome Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table / query as a prameter for PL/pgSQL function
On Aug 7, 2011, at 23:24, Ondrej Ivanič ondrej.iva...@gmail.com wrote: Hi, It is possible to pass query result (or cursor?) as function parameter? I need a function which emits zero or more rows per input row (map function from mapreduce paradigm). Function returns record (or array): (value1, value2, value3) I've tried the following: 1) create or replace function test (r record) returns setof record as $$ ... Doesn't work: PL/pgSQL functions cannot accept type record From the docs you can try using the refcursor data type though I have never done so myself. 4) use function in select clause: select my_map_func(col1, col2, col3, col4) from ... -- the rest of the query In this case I wasn't able figure out how to access record members returned by the function: select ?, ?, ?, count(*) from ( select my_map_func(col1, col2, col3, col4) as map_func_result from ... ) as map group by 1, 2, 3 The '?' should be something like map.map_func_result.value1 (both map.value1 and map_func_result.value1 doesn't not work). If function returns array then I can access value1 by using map_func_result[1] Try (map.map_func_result).value1 - the parenthesis around the table alias and column are necessary. Is there a better way how to solve this? I'm kind of satisfied with 4 (maybe 3) but it is little bit cumbersome Thanks, -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query with rightmost function does not use index
Hello
use a functional index
http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html
Regards
Pavel Stehule
2011/8/8 - - loh@hotmail.com:
For a table where column col has an index, the query:
SELECT ... FROM ... WHERE col = MD5('')
doesn't seem to use an index but
SELECT ... FROM ... WHERE col = 'd41d8cd98f00b204e9800998ecf8427e'
does.
Is this a gotcha?
I'm using PostgreSQL 8.4.8 on i686-pc-linux-gnu, compiled by GCC
gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Granting Privileges in Postgres
Dear all,
Today I researched on giving privileges in Postgres databases. I have 4
databases and near about 150 tables, 50-60 sequences and also some views
in it.
I want to give privileges to a new user in all these objects. I created
a function for that but don't know how to give privileges on all objects
all at once.
**Function for granting all privileges on all tables in
postgres database**
Step 1 : Create a new user with password
create user abc with password 'as123';
Step 2 :
create function grant_all(a text) returns void as $$
declare
name text;
user_name alias for $1;
begin
for name in select table_name from information_schema.tables where
table_schema = 'public' loop
execute 'grant all on table ' || name || ' to ' || user_name ;
end loop;
end;
$$ language plpgsql;
Step 3 :
select grant_all('abc');
Step 4 :
Finish
This will grant on tables only but Do I need to manually issue grant
commands on all objects.
I want to issue it all at once.
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Granting Privileges in Postgres
On 08/07/11 9:58 PM, Adarsh Sharma wrote: Dear all, Today I researched on giving privileges in Postgres databases. I have 4 databases and near about 150 tables, 50-60 sequences and also some views in it. I want to give privileges to a new user in all these objects. I created a function for that but don't know how to give privileges on all objects all at once. GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO rolename; this ON ALL TABLE IN SCHEMA option is new in 9.0 -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query with rightmost function does not use index
But why? The expression is not on the left side of the WHERE clause.
Hello
use a functional index
http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html
Regards
Pavel Stehule
2011/8/8 - - loh@hotmail.com:
For a table where column col has an index, the query:
SELECT ... FROM ... WHERE col = MD5('')
doesn't seem to use an index but
SELECT ... FROM ... WHERE col = 'd41d8cd98f00b204e9800998ecf8427e'
does.
Is this a gotcha?
I'm using PostgreSQL 8.4.8 on i686-pc-linux-gnu, compiled by GCC
gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit
[GENERAL] Russian Language Spam
Can someone please cancel the taxation/legal spam? None of the Russian-language posts I've read here recently have anything whatsoever to do with Postgresql. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query with rightmost function does not use index
Here may be other problem.
Show us the result of EXPLAIN Analyze.
2011/8/8, Pavel Stehule pavel.steh...@gmail.com:
Hello
use a functional index
http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html
Regards
Pavel Stehule
2011/8/8 - - loh@hotmail.com:
For a table where column col has an index, the query:
SELECT ... FROM ... WHERE col = MD5('')
doesn't seem to use an index but
SELECT ... FROM ... WHERE col = 'd41d8cd98f00b204e9800998ecf8427e'
does.
Is this a gotcha?
I'm using PostgreSQL 8.4.8 on i686-pc-linux-gnu, compiled by GCC
gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
