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
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
Import Notes
Reply to msg id not found: 4A115CFA.9080906@fmed.uba.ar
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
Show quoted text
--
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 Mellohttp://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 Mellohttp://techdocs.postgresql.org/guides/SetReturningFunctions
PostgreSQL 7.3 Set Returning Functions
Stephan SzaboStephan'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. +