Обсуждение: proposal for a CookBook in postgresql.org
I just saw a beatifull answer from Pavel, as an answer to this question: """ I'm just wondering if there's some way to retrieve the hour column as the > sum of the array values... Just like this: > > hour | statistics_date > ----------------------------+----------------- > 9000 | 2008-01-03 """ With this function: """ postgres=# create or replace function sum_items(bigint[]) returns bigint as $$ select sum($1[i])::bigint from generate_series(array_lower($1,1), array_upper($1,1)) g(i)$$ language sql immutable; CREATE FUNCTION Time: 2,510 ms postgres=# select sum_items(array[1,2,3,4]); sum_items ----------- 10 (1 row) """ I think this is a good time to propose some kind of CookBook, to preserve this kind of answers. Gerardo
On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig <gherzig@fmed.uba.ar> wrote: > I think this is a good time to propose some kind of CookBook, to > preserve this kind of answers. What, like this one? http://wiki.postgresql.org/wiki/Snippets :-) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page wrote: > On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig <gherzig@fmed.uba.ar> wrote: > >> I think this is a good time to propose some kind of CookBook, to >> preserve this kind of answers. > > What, like this one? > > http://wiki.postgresql.org/wiki/Snippets > > :-) > Oops. Yeah, kind of :) Did not see any direct link from "Docs" main page. Sory about the noise. Gerardo
2009/5/18 Gerardo Herzig <gherzig@fmed.uba.ar>: > Dave Page wrote: >> On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig <gherzig@fmed.uba.ar> wrote: >> >>> I think this is a good time to propose some kind of CookBook, to >>> preserve this kind of answers. >> >> What, like this one? >> >> http://wiki.postgresql.org/wiki/Snippets >> >> :-) >> > Oops. Yeah, kind of :) > Did not see any direct link from "Docs" main page. > > this is usual tricks for arrays - you ca use it for min, max, avg, sort for arrays. In 8.4 is possible to use generate_subscripts functions. regards Pavel Stehule others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks regards Pavel Stehule
Pavel Stehule escribió: > others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks There's a lot of good stuff in there ... would you care about copying/moving it to wiki.postgresql.org/wiki/Snippets ? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
2009/5/18 Alvaro Herrera <alvherre@commandprompt.com>: > Pavel Stehule escribió: > >> others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks > > There's a lot of good stuff in there ... would you care about > copying/moving it to wiki.postgresql.org/wiki/Snippets ? > This contents is free - and if you would do it, please do it. My english isn't good, so it's work for someone with good english. I am maintainer and founder of this site, and I am granting rights for free content copy. Regards Pavel > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
Pavel Stehule escribió: > 2009/5/18 Alvaro Herrera <alvherre@commandprompt.com>: > > Pavel Stehule escribió: > > > >> others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks > > > > There's a lot of good stuff in there ... would you care about > > copying/moving it to wiki.postgresql.org/wiki/Snippets ? > > This contents is free - and if you would do it, please do it. My > english isn't good, so it's work for someone with good english. I am > maintainer and founder of this site, and I am granting rights for free > content copy. I don't have time for that right now, but I have added a link to your page at the top of Snippets. Thanks. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > Pavel Stehule escribi?: > > 2009/5/18 Alvaro Herrera <alvherre@commandprompt.com>: > > > Pavel Stehule escribi?: > > > > > >> others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks > > > > > > There's a lot of good stuff in there ... would you care about > > > copying/moving it to wiki.postgresql.org/wiki/Snippets ? > > > > This contents is free - and if you would do it, please do it. My > > english isn't good, so it's work for someone with good english. I am > > maintainer and founder of this site, and I am granting rights for free > > content copy. > > I don't have time for that right now, but I have added a link to your > page at the top of Snippets. Thanks. FYI, I have a copy of the old plpgsql cookbook at: http://www.brasileiro.net:8080/postgres/cookbook/ PostgreSQL CookBook Pages Roberto Mello http://techdocs.postgresql.org/guides/SetReturningFunctions PostgreSQL 7.3 Set Returning Functions Stephan Szabo stored at: http://momjian.us/expire/cookbook.tgz if someone wants to transfer them to the wiki. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian escribió: > FYI, I have a copy of the old plpgsql cookbook at: > > http://www.brasileiro.net:8080/postgres/cookbook/ > PostgreSQL CookBook Pages > Roberto Mello > > http://techdocs.postgresql.org/guides/SetReturningFunctions > PostgreSQL 7.3 Set Returning Functions > Stephan Szabo Stephan's article is already on the wiki: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions It needs an update. I don't know about Roberto Mello's site. Did we get a copyright transfer or a license saying we could use the contents? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Bruce Momjian escribi?: > > > FYI, I have a copy of the old plpgsql cookbook at: > > > > http://www.brasileiro.net:8080/postgres/cookbook/ > > PostgreSQL CookBook Pages > > Roberto Mello > > > > http://techdocs.postgresql.org/guides/SetReturningFunctions > > PostgreSQL 7.3 Set Returning Functions > > Stephan Szabo > > Stephan's article is already on the wiki: > http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions > It needs an update. > > I don't know about Roberto Mello's site. Did we get a copyright > transfer or a license saying we could use the contents? Nope, but I assumed it was BSD-licensed. I see this Josh Berkus copyright: --Copyright Josh Berkus, josh@agliodbs.com --permission granted to use anywhere provided that this --copyright statement remains in the code. --No warranty is given or implied. --Use at your own risk -- strictly beta code. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian escribió: > Alvaro Herrera wrote: > > I don't know about Roberto Mello's site. Did we get a copyright > > transfer or a license saying we could use the contents? > > Nope, but I assumed it was BSD-licensed. I see this Josh Berkus > copyright: > > --Copyright Josh Berkus, josh@agliodbs.com You're saying that Josh Berkus owns the copyright of the entire site? That seems unlikely. I think the way we should go about this is somebody talks to Roberto and gets his approval on us using his content on the Wiki (the way we did with Pavel upthread). Otherwise I think it's a nonstarter. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > Bruce Momjian escribi?: > > Alvaro Herrera wrote: > > > > I don't know about Roberto Mello's site. Did we get a copyright > > > transfer or a license saying we could use the contents? > > > > Nope, but I assumed it was BSD-licensed. I see this Josh Berkus > > copyright: > > > > --Copyright Josh Berkus, josh@agliodbs.com > > You're saying that Josh Berkus owns the copyright of the entire site? > That seems unlikely. On one function. > I think the way we should go about this is somebody talks to Roberto and > gets his approval on us using his content on the Wiki (the way we did > with Pavel upthread). Otherwise I think it's a nonstarter. OK. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +