[rod k]

plphp has just been released for those of you who are interested. I haven't tried it myself yet, as I'm having problems building it under FreeBSD but for anyone running Linux they have binaries available.

[Jabol]

hi,
I was running and I must say that it rocks!
But I'm a bit disappointed as well. I cannot build my own version ( or at least don't know how ) and there is no documentation. It all works till I'm not creating functions using arguments and I dunno why becouse there's no docs.
I wish there will be a day in which it'll be bundled into postgres main distribution and I'll be able to build it with my own php version without any problems.

[rod k]

there is no documentation

It's still beta... I'm sure docs are forthcoming

It all works till I'm not creating functions using arguments

Are you saying functions with arguments don't work or functions without arguments?

[Jabol]

I mean functions with arguments, but mayby it's just that I dunno how to use tham. I tried $argv[1] and $1 and neither worked

[rod k]

Hmm, did you try func_get_arg() or fung_get_args()?

[Jabol]

hmm... no. I'll try it soon.

[rycamor]

Thanks rod; this is some fairly rocking news. I hadn't expected this to happen so soon. Of course, I too will reserve judgement until I can run it on FreeBSD .

[rod k]

Hehe, I'm so itchin' to try it that I'm thinking about installing slackware on one of my boxes....

[rycamor]

Originally posted by rod k
Hehe, I'm so itchin' to try it that I'm thinking about installing slackware on one of my boxes....

Slackware isn't bad... I use it for one of my desktops. The only problem is... sigh... nothing remotely like the FreeBSD ports system. Slackware + ports would be a cool Linux distro. Oops... the OT meter is dipping into the red.

[bcyde]

So when this does come out and starts to get polished what's going to be the PG procedural language of choice? A few postings in the last couple of weeks on the PG mailing list led me to believe that plpgsql development is kind of slow at the moment since there's no dedicated developer on it and for certain functions I've seen people recommend plctl and/or plperl over plpgsql.

I haven't had to do much with my own user defined functions and only experimented with a few simple ones in plpgsql but will there eventually be 1 language of choice or is it going to be "use what you're familiar", or "this language is good at this, but can't do this" sort of thing?

-b

[rod k]

Well, plpgsql is only good for internal DB stuff. If you want to write "untrusted" procedures (e.g. sending emails) then you need to use something else. I think for most applications plphp will work fine, tho I could see plperl being superior in certain situations. I'm much better with PHP than I am with Perl, so I'm really looking forward to this.


BTW, I've just about got plphp to build on FreeBSD 4.8 I'll zip the .so file and attach it here when I get it completed.

[rycamor]

bcyde raises a good point. I have to admit that I have some mixed feelings about PostgreSQL's procedural approach. While I think its great that we can do procedures in many languages, I hope the team doesn't lose focus on making Pl/PgSQL a bulletproof tank of a language .

It seems to me that most database procedural languages are rather on the strict side, for obvious reasons (no implicit type conversion, etc...). Also, they tend to be more like Functional/declarative languages, deriving some ancestry from LISP, etc... I consider this to be a Good Thing, in the sense that a procedural language should be about supporting business logic. Business logic is the sort of thing that should be "ironclad". Also, IMHO, the strict methods used allow the system to do more optimizing for speed and stability, but mainly allow the programmer to focus more on logic and less on such "manual" things as incrementing variables in loops, etc... Ask anyone who uses PL/SQL for Oracle, and they would wonder why on earth you would need another language for database back-end logic.

Languages like PHP and Perl tend to be a lot more flexible in how you approach variables, types, and structure, and allow you to be in general just a little more arbitrary about how you do things. I would say that PHP and Perl should be used for things that are harder to do in the main procedural language. Generally, I'm thinking of tasks that are more "un-database-like" and involve more messy details. Triggers that send emails or connect to the filesystem in some way, etc... are just a few examples. Also, procedures that play with strings, regular expressions, etc... might be more suited to Perl/PHP type languages.

So, I think there should be two major areas of concern for stored procedures:
1. core business logic, more appropriate for Pl/PgSQL, etc...
2. messy details, more suited to Perl and PHP, and untrusted environments, as rod mentions.

Not sure about languages like TCL or Python, though. Python is more of a functional language, and also allows for more strictness of typing, etc... so maybe it falls somewhere in between. TCL is supposedly somewhat functional, but I know very little about it.

So, given this, I believe I will still be attempting most of my serious procedures in Pl/PgSQL, but I will be playing around with PHP for some of the more "fun" areas of stored procedures. I hope the PostgreSQL developers don't give up on advancing Pl/PgSQL. I think what Pl/PgSQL needs is more power in the area of expressing abstract logic, and in the ability to interact more smoothly with other procedures.

Mainly, though, I think the whole procedural interface could use a little more thought, in terms of how the procedural system interacts with the rest of the database. Apparently (and I say this without extensive Oracle experience) it is much more complete and seamless in Oracle, with more logical control over parameters, naming, nested transactions, etc...

On a final note, here is a current list of procedural languages available PostgreSQL:

1. SQL
2. Pl/PgSQL
3. PL/R -- for heavy-duty statistical computing (still in development)
4. C -- when you really need nuts-n-bolts
5. Java (still in development)
6. Perl
7. Python
8. PHP (still in development)
9. TCL

It'll be interesting to see where all this leads...

[Jabol]

Yeah, you're right.
The main application should be build using sql/plpgsql only. Another languages, like php or perl (or the others) may be useful while doing something out of database, for example sending email, reading file or making a net connection. This is something that you cannot do using sql/plpgsql.
I've never used and never needed any another language with postgresql so I agree with you.

[rod k]

Jabol,

Per a post on the plphp mailing list, it looks like the function arguments are passed as $arg0, $arg1, $arg2, ... $argn

[rycamor]

Originally posted by rod k
Jabol,

Per a post on the plphp mailing list, it looks like the function arguments are passed as $arg0, $arg1, $arg2, ... $argn

Yeah, that would make sense, since PHP cannot have a variable name start with a number. But, I would kind of prefer something like $arg[0] ... $arg[n].

One thing I really like about PHP is the nice array-manipulation methods you get. I wonder how well this will work with PostgreSQL's array type, and functions dealing with arrays.