Thread: Re: Best Procedural Language?
Martha Stewart called it a Good Thing when "Carlo Stonebanks" <cstonebanks@nissenfasteners.com> wrote:
> I am interested in finding out a "non-religious" answer to which
> procedural language has the richest and most robust implementation
> for Postgres. C is at the bottom of my list because of how much
> damage runaway code can cause. I also would like a solution which is
> platorm-independent; we develop on Windows but may deploy on Linux.
You mean for implementing stored procedures?
I'd say that the answer varies depending on what the stored proc is
for.
- If it needs to do "text munging," then one of {Perl|Python|Tcl} seem
appropriate; they draw in big libraries of text munging code
- If you're writing code that selects data from various tables based
on the inputs, then pl/pgsql tends to be the natural answer
- C is needed when you need deep engine access that can't be gotten
any other way
- Untrusted Perl/Tcl are nifty if you need access to the rich sets of
external libraries
- If you have some code in Java that you'd want to run in the DB
server, then one of the pl/Java systems may be for you
It doesn't seem overly flameworthy to me.
Except for the cases where you *must* use C, you can usually
accomplish things in the "wrong" language, but there are likely to be
drawbacks...
- Doing funky string munging using the SQL functions available in
pl/pgsql is likely to be painful;
- Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such
requires having an extra level of function manipulations that
won't be as natural as straight pl/pgsql.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/postgresql.html
Q: Are the SETQ expressions used only for numerics?
A: No, they can also be used with symbolics (Fig.18).
-- Ken Tracton, Programmer's Guide to Lisp, page 17.
On 8/1/06, Christopher Browne <cbbrowne@acm.org> wrote: > Martha Stewart called it a Good Thing when "Carlo Stonebanks" <cstonebanks@nissenfasteners.com> wrote: > > I am interested in finding out a "non-religious" answer to which > > procedural language has the richest and most robust implementation > > for Postgres. C is at the bottom of my list because of how much > > damage runaway code can cause. I also would like a solution which is > > platorm-independent; we develop on Windows but may deploy on Linux. > > > - Doing funky string munging using the SQL functions available in > pl/pgsql is likely to be painful; > > - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such > requires having an extra level of function manipulations that > won't be as natural as straight pl/pgsql. Another important distinguishing characteristic is whether it supports set returning functions. I think only plpgsql does right now.
On 8/1/06, Ian Harding <harding.ian@gmail.com> wrote: > On 8/1/06, Christopher Browne <cbbrowne@acm.org> wrote: > > Martha Stewart called it a Good Thing when "Carlo Stonebanks" <cstonebanks@nissenfasteners.com> wrote: > > > I am interested in finding out a "non-religious" answer to which > > > procedural language has the richest and most robust implementation > > > for Postgres. C is at the bottom of my list because of how much > > > damage runaway code can cause. I also would like a solution which is > > > platorm-independent; we develop on Windows but may deploy on Linux. > > > > > > > > - Doing funky string munging using the SQL functions available in > > pl/pgsql is likely to be painful; > > > > - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such > > requires having an extra level of function manipulations that > > won't be as natural as straight pl/pgsql. > > Another important distinguishing characteristic is whether it supports > set returning functions. I think only plpgsql does right now. and C, and SQL ;) in fact, sql functions make the best SRF because they are fast, basically as fast as a query, but also can be called like this: select sql_func(); --works! select plpgsql_func(); --bad select * from plpgsqlfunc(); works, but the other form is nice in some situations merlin
On 8/1/06, Christopher Browne <cbbrowne@acm.org> wrote: > Martha Stewart called it a Good Thing when "Carlo Stonebanks" <cstonebanks@nissenfasteners.com> wrote: > > I am interested in finding out a "non-religious" answer to which > > procedural language has the richest and most robust implementation > > for Postgres. C is at the bottom of my list because of how much > > damage runaway code can cause. I also would like a solution which is > > platorm-independent; we develop on Windows but may deploy on Linux. my take: C: you can probably get by without doing any C. Most (but not quite all) of things you would do via C is exposed in libraries. One thing you can do with C for example is invoke a function via its oid and manually supplying parameters to make callbacks for proceures. you can also dump core on your backend. good luck! pl/pgsql: you do not know postgresql if you do not know pl/pgsql. period. ideal for data processing and all sorts of things. all queries are first class in the code (except for dynamic sql), which in my estimation cuts code size, defect rate, and development time about 75% for typical database type stuff. just be warned, after you learn it you will never want to use another database ever again, i'm not kiddig. pl/perl, etc: not much to add beyond what chris browe said: great for text processing or library support. merlin
Well, the surprise for me is: this is exactly what I thought! I actually do have a lot of string manipulation to do, but I am the only one on the team with Tcl experience. For the sake of other developers I thought that the plPHP project would be interesting, but I don't get the impression that it is as well-developed as plTcl. Does anyone know anything about it? Also, does anyone know why the plTcl was taken outof the core distribution? Carlo ""Ian Harding"" <harding.ian@gmail.com> wrote in message news:725602300608012014v6337f4d0r90c4c8dc3c25884a@mail.gmail.com... > On 8/1/06, Christopher Browne <cbbrowne@acm.org> wrote: >> Martha Stewart called it a Good Thing when "Carlo Stonebanks" >> <cstonebanks@nissenfasteners.com> wrote: >> > I am interested in finding out a "non-religious" answer to which >> > procedural language has the richest and most robust implementation >> > for Postgres. C is at the bottom of my list because of how much >> > damage runaway code can cause. I also would like a solution which is >> > platorm-independent; we develop on Windows but may deploy on Linux. >> > > >> >> - Doing funky string munging using the SQL functions available in >> pl/pgsql is likely to be painful; >> >> - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such >> requires having an extra level of function manipulations that >> won't be as natural as straight pl/pgsql. > > Another important distinguishing characteristic is whether it supports > set returning functions. I think only plpgsql does right now. > > ---------------------------(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 >
>> - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such
>> requires having an extra level of function manipulations that
>> won't be as natural as straight pl/pgsql.
>
> Another important distinguishing characteristic is whether it supports
> set returning functions. I think only plpgsql does right now.
Actually no. Plperl, plphp, plruby and I believe even pl/Tcl support set
returning functions.
Sincerely,
Joshua D. Drake
>
> ---------------------------(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
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Joshua D. Drake wrote: > >>> - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such >>> requires having an extra level of function manipulations that >>> won't be as natural as straight pl/pgsql. >> >> >> Another important distinguishing characteristic is whether it supports >> set returning functions. I think only plpgsql does right now. > > > Actually no. Plperl, plphp, plruby and I believe even pl/Tcl support set > returning functions. and so does PL/R ;-) Joe
Merlin Moncure wrote: > On 8/1/06, Ian Harding <harding.ian@gmail.com> wrote: > >On 8/1/06, Christopher Browne <cbbrowne@acm.org> wrote: > >> Martha Stewart called it a Good Thing when "Carlo Stonebanks" > ><cstonebanks@nissenfasteners.com> wrote: > >> > I am interested in finding out a "non-religious" answer to which > >> > procedural language has the richest and most robust implementation > >> > for Postgres. C is at the bottom of my list because of how much > >> > damage runaway code can cause. I also would like a solution which is > >> > platorm-independent; we develop on Windows but may deploy on Linux. > >> > > > > > >> > >> - Doing funky string munging using the SQL functions available in > >> pl/pgsql is likely to be painful; > >> > >> - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such > >> requires having an extra level of function manipulations that > >> won't be as natural as straight pl/pgsql. > > > >Another important distinguishing characteristic is whether it supports > >set returning functions. I think only plpgsql does right now. > > and C, and SQL ;) And PL/Perl (and PL/php but it's still immature.) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Carlo Stonebanks wrote:
> Well, the surprise for me is: this is exactly what I thought! I actually do
> have a lot of string manipulation to do, but I am the only one on the team
> with Tcl experience. For the sake of other developers I thought that the
> plPHP project would be interesting, but I don't get the impression that it
> is as well-developed as plTcl. Does anyone know anything about it?
plPHP is not as mature as plTcl (or is that plTclng). However it is very
well developed and maintained. Heck, companies are even holding talks
and training classes on it now.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Christopher Browne wrote:
Just thought I'd mention that while we're on the topic.
I would dearly love to see plPHP join the family as a real supported language, right in the distribution.Martha Stewart called it a Good Thing when "Carlo Stonebanks" <cstonebanks@nissenfasteners.com> wrote:I am interested in finding out a "non-religious" answer to which procedural language has the richest and most robust implementation for Postgres. C is at the bottom of my list because of how much damage runaway code can cause. I also would like a solution which is platorm-independent; we develop on Windows but may deploy on Linux.
Just thought I'd mention that while we're on the topic.
Attachment
> I would dearly love to see plPHP join the family as a real supported
> language, right in the distribution.
You mean as opposed a real support language outside of the distribution?
Being in core doesn't really gain us much except a prestige moniker.
But to answer your question, there is some work that needs to be done first.
Joshua D. Drake
>
> Just thought I'd mention that while we're on the topic.
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(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
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
> plPHP is not as mature as plTcl (or is that plTclng). However it is very > well developed and maintained. Heck, companies are even holding talks and > training classes on it now. What is lacking in plPHP? To be honest, even though I am a Tcl developer I would rather develop in PHP, and I know next to NOTHING about PHP! The thing is that Tcl leaves a bad taste in a lot of programmer's mouths - and I can't blame them. PHP looks and behaves like a "normal" programming language, so there's more likelyhood that other programmers will be able to maintain my code. (Imagine that - a developer worrying about how the NEXT developer will maintain his code! Think the idea will catch on?) I couldn't find a recent release of plPHP, and have no idea of its status. Carlo
Carlo Stonebanks wrote:
Last release was 2005. This is the first release that is actually useful, IMHO, because it allows SQL commands buried in the code, prior releases did not.
My own totally unscientific I-didn't-get-very-thorough result from a trial installation was that it was sloooooow, as in less than half the speed of some comparable code in plperl. But I never isolated what was causing the slowdown and so I can't really say much more. It was bad enough though that I abandoned it very quickly, sucked in my gut and coded some perl.
ww.commandprompt.com/community/plphp/plPHP is not as mature as plTcl (or is that plTclng). However it is very well developed and maintained. Heck, companies are even holding talks and training classes on it now.What is lacking in plPHP? To be honest, even though I am a Tcl developer I would rather develop in PHP, and I know next to NOTHING about PHP! The thing is that Tcl leaves a bad taste in a lot of programmer's mouths - and I can't blame them. PHP looks and behaves like a "normal" programming language, so there's more likelyhood that other programmers will be able to maintain my code. (Imagine that - a developer worrying about how the NEXT developer will maintain his code! Think the idea will catch on?) I couldn't find a recent release of plPHP, and have no idea of its status.
Last release was 2005. This is the first release that is actually useful, IMHO, because it allows SQL commands buried in the code, prior releases did not.
My own totally unscientific I-didn't-get-very-thorough result from a trial installation was that it was sloooooow, as in less than half the speed of some comparable code in plperl. But I never isolated what was causing the slowdown and so I can't really say much more. It was bad enough though that I abandoned it very quickly, sucked in my gut and coded some perl.
Carlo
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org Attachment
>> > ww.commandprompt.com/community/plphp/ > > Last release was 2005. This is the first release that is actually > useful, IMHO, because it allows SQL commands buried in the code, prior > releases did not. > That is actually wrong. Go here: http://projects.commandprompt.com/public/plphp There was a release just recently of a bunch of code that fixed a bunch of stuff but was never released. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Carlo Stonebanks wrote:
>> plPHP is not as mature as plTcl (or is that plTclng). However it is very
>> well developed and maintained. Heck, companies are even holding talks and
>> training classes on it now.
>
> What is lacking in plPHP? To be honest, even though I am a Tcl developer I
> would rather develop in PHP, and I know next to NOTHING about PHP!
There are some known issues that cause it to crash, you can see the
project site for an example.
Also it requires the apache apxs stuff which doesn't make it the most
portable.
We are looking at ways to make it not have the apache requirement.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Joshua D. Drake wrote: > >>> >> ww.commandprompt.com/community/plphp/ >> >> Last release was 2005. This is the first release that is actually >> useful, IMHO, because it allows SQL commands buried in the code, >> prior releases did not. >> > > That is actually wrong. Go here: Well I took it from your home page! :)
Attachment
Carlo Stonebanks wrote: > > plPHP is not as mature as plTcl (or is that plTclng). However it is very > > well developed and maintained. Heck, companies are even holding talks and > > training classes on it now. > > What is lacking in plPHP? To be honest, even though I am a Tcl developer I > would rather develop in PHP, and I know next to NOTHING about PHP! > > The thing is that Tcl leaves a bad taste in a lot of programmer's mouths - > and I can't blame them. PHP looks and behaves like a "normal" programming > language, so there's more likelyhood that other programmers will be able to > maintain my code. (Imagine that - a developer worrying about how the NEXT > developer will maintain his code! Think the idea will catch on?) > > I couldn't find a recent release of plPHP, and have no idea of its status. It sort of works if your platform is not too deviated from the mainstream Linux stuff (i.e. you're not using threaded PHP for example), and you avoid the stuff that we know cause server crashes. Also, array handling is very suboptimal -- it works for the simplest cases but fails if you get too clever (where "too clever" is not really very clever). There are probably other buggy areas I forget. I would suggest you to report the bugs you find, of which there will be plenty, but on the other hand it will be a waste of your time because there aren't any development resources devoted to it currently. I haven't measured performance at all. Oh, and it's called PL/php, not plPHP. In short, I suggest you look at PL/Perl. It is also a "normal" programming language. Joshua Drake wrote: > > However it is very well developed and maintained. I disagree. And I was the maintainer last time I checked, so you'd say my opinion carries some weight. > > Heck, companies are even holding talks and training classes on it > > now. Companies are run merely to make money. The fact that some of them make money by training people to use broken products does not make the products any less broken. While I am happy that people use PL/php, I would be much happier if it wasn't broken. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
> I disagree. And I was the maintainer last time I checked, so you'd say
> my opinion carries some weight.
Because it is not currently being worked does not mean it is not
maintained. It means that it is not currently being worked on. It is by
no means a dead project.
>
>>> Heck, companies are even holding talks and training classes on it
>>> now.
>
> Companies are run merely to make money.
That is a farse. Companies exist at the benefit of the shareholders. The
shareholders may deem that their main goal is making money in which case
your argument is valid. However if CMD took that approach I would be far
richer and doing a lot more then PostgreSQL. We could easily double our
revenue just by offering MySQL + PostgreSQL support and development.
> The fact that some of them make
> money by training people to use broken products does not make the
> products any less broken. While I am happy that people use PL/php, I
> would be much happier if it wasn't broken.
Well that would require not using PHP at all wouldn't it? ;) However,
Alvaro is correct there are currently no resources dedicated to PL/php.
There will be in the future but for now we are busy with other things.
It is however, BSD licensed please feel free to actually contribute.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Joshua D. Drake wrote: > However, Alvaro is correct there are currently no resources dedicated > to PL/php. > There will be in the future but for now we are busy with other things. Well consider me your biggest cheerleader, and when circumstances bring it to the front burner that will be great.
Attachment
Joshua, where can I find docs on how to return SETOF from Tcl? ""Joshua D. Drake"" <jd@commandprompt.com> wrote in message news:44D023B3.6050809@commandprompt.com... > >>> - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such >>> requires having an extra level of function manipulations that >>> won't be as natural as straight pl/pgsql. >> >> Another important distinguishing characteristic is whether it supports >> set returning functions. I think only plpgsql does right now. > > Actually no. Plperl, plphp, plruby and I believe even pl/Tcl support set > returning functions. > > Sincerely, > > Joshua D. Drake > > >> >> ---------------------------(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 >> > > > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > > > ---------------------------(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 >
I'd say that the biggest benefit of pl/pgsql for postgres is that it is so close to Oracle's own procedural language. This makes the job of porting from Oracle to postgres *nearly* trivial. Convincing a site to switch from Oracle to Postgres is therefroe easier and a major feather in postgres's cap. Working with both Oracle 8,9,10 and postgres 7.4 and 8, I find switching between the two dbs fairly easy. Oracle is richer in terms of (programming) features but bang for buck and ease of administration/setup etc, you just can't beat postgres... John Merlin Moncure wrote: > On 8/1/06, Christopher Browne <cbbrowne@acm.org> wrote: > >> Martha Stewart called it a Good Thing when "Carlo Stonebanks" >> <cstonebanks@nissenfasteners.com> wrote: >> > I am interested in finding out a "non-religious" answer to which >> > procedural language has the richest and most robust implementation >> > for Postgres. C is at the bottom of my list because of how much >> > damage runaway code can cause. I also would like a solution which is >> > platorm-independent; we develop on Windows but may deploy on Linux. > > > my take: > C: > you can probably get by without doing any C. Most (but not quite all) > of things you would do via C is exposed in libraries. One thing you > can do with C for example is invoke a function via its oid and > manually supplying parameters to make callbacks for proceures. you > can also dump core on your backend. good luck! > > pl/pgsql: > you do not know postgresql if you do not know pl/pgsql. period. ideal > for data processing and all sorts of things. all queries are first > class in the code (except for dynamic sql), which in my estimation > cuts code size, defect rate, and development time about 75% for > typical database type stuff. just be warned, after you learn it you > will never want to use another database ever again, i'm not kiddig. > > pl/perl, etc: > not much to add beyond what chris browe said: great for text > processing or library support. > > merlin > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
On 8/3/06, John Sidney-Woollett <johnsw@wardbrook.com> wrote: > I'd say that the biggest benefit of pl/pgsql for postgres is that it is > so close to Oracle's own procedural language. This makes the job of > porting from Oracle to postgres *nearly* trivial. while this is true, pl/pgsql has a lot of other advantages wrt other sp languages: * first class, non dynamic sql: cuts code, complexity and errors down 50% and increases security (or at least makes being secure easier) * unified error system: perl brings perl errors, java brings java errors, etc * cursors/refcursors: you can pass anonymous refcursors between pl/pgsql functions directly. of course, this can be emulated in dynamic plpgsql, but it is awkward and error prone. also, different cursor mechanisms bring huge flexiblity in tight syntax. * named parameters * and most of all, pl/pgsql makes programming fun. ok, this is entirely subjective and inconsequential, but it works for me. :-) merlin