Stored Procedures/Functions (was [GENERAL] about speed) - Mailing list pgsql-general
From | chewie@wookimus.net |
---|---|
Subject | Stored Procedures/Functions (was [GENERAL] about speed) |
Date | |
Msg-id | 199911252201.QAA06239@wolfheim.wookimus.net Whole thread Raw |
In response to | about speed ("Vovk G. Grigoriy" <vovk@sled.rpa.ryazan.su>) |
List | pgsql-general |
("[n]" denotes a footnote.) Vovk! I believe your question was directed toward finding an equivalent to Micro$oft's Stored Proceedures, correct? In short, yes, Postgresql does have an equivalent to M$'s stored procedures. Micro$oft cooked up a procedural language called TSQL. Postgresql has a number of options where Microsoft only had one. With Postgresql, you can use the PL that is packaged with the engine called PL/pgSQL [1]. If you know Tcl, you can use PL/Tcl [2]. And if you feel real adventurous and would like to use functions and triggers written using programming language functions in C or C++ rather than procedural (interpreted) language functions, you can use the libpg library or the esql library (embedded SQL). Just check out the Programmer's Manual in the documentation [3]. I couldn't comment on the speed of things, as I haven't yet tried any of these methods (it's only a matter of time -- a very short time -- before I do, though.) I do understand that PL/pgSQL was created with the same byte-compiling methods that TSQL was. Here's a little snippet from the PL/pgSQL section of the Programmer's Manual[1]: For all expressions and SQL statements used in the function, the PL/pgSQL bytecode interpreter creates a prepared execution plan using the SPI managers SPI_prepare() and SPI_saveplan() functions. This is done the first time, the individual statement is processed in the PL/pgSQL function. Thus, a function with conditional code that contains many statements for which execution plans would be required, will only prepare and save those plans that are really used during the entire lifetime of the database connection. This looks VERY similar to M$SQL Server's stored procedure implementation. I looked at the differences between the two languages, and I don't think there are too many that they cannot be overcome with a little familiarity. In fact, I think there is more logic to the PL/pgSQL language than TSQL. Take for example how you assign variables. In TSQL you must use the following statement: SELECT @<identifier>=<expression> Now, doesn't that seem a bit odd? Using the SELECT statement to assign values to identifiers is certainly a way to confuse the programmer, and I'm willing to bet that such use of the SELECT statement is not SQL92 compliant. (see question about standards below, please) In PL/pgSQL you use the following: <identifier> := <expression> And that looks like just a small sample of the language. I'm getting very anxious to try a bit more. ;-) Oh, and now on to my question: where can one find an on-line copy of these often quoted standards, such as SQL92, SQL3, etc? Chad Footnotes: ----------------------------------------------------------------------- [1] http://www.postgresql.org/docs/programmer/xplang1501.htm [2] http://www.postgresql.org/docs/programmer/xplang1771.htm [3] http://www.postgresql.org/docs/programmer/index.html -------------------------------------------------------------------------- Chad Walstrom mailto:chewie@wookimus.net a.k.a ^chewie, gunnarr http://wookimus.net/~chewie Gnupg fingerprint = B4AB D627 9CBD 687E 7A31 1950 0CC7 0B18 206C 5AFD --------------------------------------------------------------------------
Attachment
pgsql-general by date: