On Tue, Apr 11, 2006 at 12:47:03AM -0400, Tom Lane 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
With all due respect, SQL *is* Turing-complete. Here's a little demo
of this Turing-completeness:
CREATE TABLE fib_mem( n numeric PRIMARY KEY, fib_n numeric NOT NULL
);
CREATE OR REPLACE FUNCTION memoize_fib(n numeric, fib_n numeric)
RETURNS numeric
STRICT
LANGUAGE SQL
AS $$ INSERT INTO fib_mem VALUES ($1, $2); SELECT $2;
$$;
CREATE OR REPLACE FUNCTION fib(numeric)
RETURNS numeric
LANGUAGE SQL
AS $$ SELECT COALESCE( (SELECT fib_n FROM fib_mem WHERE n=$1), memoize_fib( $1, CASE
WHEN$1 < 2 THEN $1 ELSE fib($1-2) + fib($1-1) END ) );
$$;
> --- plpgsql is. So if our would-be hacker has a need to do some
> computation incidental to his hack, he can certainly get it done in
> plpgsql, but not necessarily in plain SQL.
>
> I don't feel a need to offer specific examples as requested by
> Andrew. 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.
The cat is already out of the bag with SQL because it has branching
and recursion, which is enough for Turing-completeness. Whether we
decide to include PL/whatever by default or not shouldn't be
predicated on the wrong assumption that these PLs have more power
inside the database than SQL does.
That said, I believe it's a *great* idea not to include untrusted PLs
by default :)
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter
Remember to vote!