Re: [PATCHES] prepareable statements

On Sat, Jul 20, 2002 at 10:00:01PM -0400, Tom Lane wrote:
> AFAICT, the syntax we are setting up with actual SQL following the
> PREPARE keyword is *not* valid SQL92 nor SQL99. It would be a good
> idea to look and see whether any other DBMSes implement syntax that
> is directly comparable to the feature we want. (Oracle manuals handy,
> anyone?)

I couldn't find anything on the subject in the Oracle docs -- they have
PREPARE for use in embedded SQL, but I couldn't see a reference to
PREPARE for usage in regular SQL. Does anyone else know of an Oracle
equivalent?

> Assuming we do not find any comparable syntax to steal, my inclination
> would be to go back to your original syntax and use "AS" as the
> delimiter. That way we're not creating problems for ourselves if we
> ever want to implement the truly spec-compliant syntax (in ecpg, say).

Ok, sounds good to me.

> * This is certainly not legal C:
>
> + if (Show_executor_stats)
> + ResetUsage();
> +
> + QueryDesc *qdesc = CreateQueryDesc(query, plan, dest, NULL);
> + EState *state = CreateExecutorState();
>
> You must be using a C++ compiler.

Well, it's legal C99 I believe. I'm using gcc 3.1 with the default
CFLAGS, not a C++ compiler -- I guess it's a GNU extension... In any
case, I've fixed this.

> * What if the stored query is replaced between the time that
> transformExecuteStmt runs and the time the EXECUTE stmt is actually
> executed?

Good point ... perhaps the easiest solution would be to remove
DEALLOCATE. Since the backend's prepared statements are flushed when the
backend dies, there is little need for deleting prepared statements
earlier than that. Users who need to prevent name clashes for
plan names can easily achieve that without using DEALLOCATE.

Regarding the syntax for EXECUTE, it occurs to me that it could be made
to be more similar to the PREPARE syntax -- i.e.

PREPARE foo(text, int) AS ...;

EXECUTE foo('a', 1);

(rather than EXECUTE USING -- the effect being that prepared statements
now look more like function calls on a syntactical level, which I think
is okay.)

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC