On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> What does enabling plpgsql do via access that you can't just do from an
>> SQL query?
>
> SQL isn't Turing-complete
SQL with the ability to create recursive functions, as exists in pg, is
certainly turing-complete (within the usual practical sense of the term,
since no real machine has unlimited storage space).
A formal proof is left as an exercise for the reader; but several examples
of the power of SQL (not pl/pgsql) functions for performing iterative
operations can be found in the newsysviews source, along with a working
implementation of generate_series for 7.4 in plain SQL. (For convenience
that implementation has a limited range, but merely adding a few more
cross joins would extend that range as far as desired.)
Pl/pgsql may offer notational conveniences, but it has no real computational
power above plain SQL functions.
> I don't feel a need to offer specific examples as requested by Andrew.
Why not? You're basing your entire argument on a false premise (that
pl/pgsql is more powerful than SQL); I can provide specific examples of
why this is not the case, or refute any that you care to provide. For
example, here is an SQL function to generate all alphabetic strings of
a specified length:
create function alpha(integer) returns setof text language sql as $$ select x || chr(c) from alpha($1-1) s1(x),
generate_series(97,122) s2(c) where $1 > 0 union all select '' where $1 <= 0
$$;
(and yes, I can do it without generate_series if need be)
That takes ~97 seconds to execute alpha(5) on one of my machines, whereas
a simple generate_series returning the same number of rows takes ~30
seconds, so the performance is not at all bad.
> The point here is that we're offering a significantly more powerful
> swiss army knife when we include plpgsql (or any other PL), and it's
> hard to foresee the implications of that with any certainty.
pl/pgsql is not comparable to other PLs in this case. Specifically, it
does not provide access to any functionality that is not already part of
Postgres itself.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services