Re: plpgsql by default

On Tue, Apr 11, 2006 at 12:47:03AM -0400, Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!