Re: psql as an execve(2) interpreter

Jonah,

Thanks for your comments.

Jonah H. Harris writes:
> I have a lot of shell scripts that run as cron jobs and have considered
> this option. However, if you look at it carefully, SQL is totally
> different from say perl, php, bash, etc. for scripts which execute from
> the shell. Tom is right, it is much more valuable and supportable to
> call psql from within a shell script than add the functionality to psql
> itself.

I, too, have thought a lot about this and I suppose we are reaching
different conclusions. I would very much appreciate hearing your
logic, as the underlying reasoning you imply is not transparent to me.
For what it is worth, here is an outline of part of my thinking.

It is true that the nature of SQL as a language is different from
other traditional programming languages, as it does not have concepts
such as variables and flow control. To my way of thinking, however,
that simply dictates what is possible to express in the language.
Importantly, I do not see how it influences how one might wish to
execute the commands given in the language (or any language for that
matter). In my mind the decision about how to execute something is
based on what it does and what the larger context is, not what the
language can express.

Suppose I have a script S in some language L that is interpretable by
some interpreter I. The language L might be SQL and the inrepreter I
might be psql, but nothing that follows depends on that. Indeed, the
language could be perl and the interpreter perl. The fundamental
questions are:

- What are useful ways to have the interpreter I carry out the
instructions contained within S?

- What determines why those are useful?

- How can those means be achieved?

For most scripting languages L (e.g., shell commands, perl, etc.) the
prior art has identified two useful means of having the interpreter
execute the instructions in S: 1) explicit execution (i.e., execute
the interpreter and explicitly pass the appropriate script S to it)
and 2) implicit execution (i.e., execute the script and magically have
the system invoke the interpreter on it). Interpreting SQL scripts
stands out as one exception to this.

Why would one choose one method over another? In all cases that I can
think of, the decision to use one method over another depends entirely
on considerations that are external to the nature of the language L
itself. I would venture to say that they are governed primarily by
the nature of the external interface one is trying to create. In some
cases, depending on what the script actually does, it is much more
natural to invoke a script directly. An example would be one that is
a wrapper to something else, but must take the responsibility for
setting up the environment first. In other cases, the other mechanism
is more natural. The decision does not bear on what the _language_ is
capable of expressing, but rather on what the particular script is
doing and how it fits into a larger external context.

In my mind, the same is true for SQL. In some cases it is appropriate
to execute the interpreter (i.e., psql) explicitly (that is currently
our only option). In other cases it is appropriate to execute it
implicitly. I see no fundamental difference between this and any
other interpreter.

Clearly, an implicit execution mechanism for SQL cannot work quite as
transparently as for languages that use the hash mark (#) as a comment
introducer. However, supporting this option need not interfere with
other uses of the interpreter nor need it be costly. What is required
is 1) a command line option that differentiates traditional behavior
from the "implicit interpreter" behavior, and 2) a modification of how
the first line of the file is handled depending on the mode. No other
changes are required; no interaction with the vast majority of the
code is needed.

Thus, my analysis suggests no fundamental difference between the set
of invocations that are useful for the majority of interpreters and
the set that would be useful for interpreters of SQL. I also can
envision a means of expanding that set for psql that would have no
impact on either its normal use or its ongoing maintenance and
development. Consequently, I see no compelling reason not to move in
this direction. However, I must be missing something obvious, as
there seems to be conflicting sentiment. I would be very interested
to learn more about what is behind those ideas.

Cheers,
Brook