Stored Procedures/Functions (was [GENERAL] about speed)

("[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(at)wookimus(dot)net
a.k.a ^chewie, gunnarr http://wookimus.net/~chewie

Gnupg fingerprint = B4AB D627 9CBD 687E 7A31 1950 0CC7 0B18 206C 5AFD
--------------------------------------------------------------------------