psql Feature request \set query

Josh Berkus wrote:

Hackers,

Just had a feature request from Wheeler, and I don't see why it shouldn't
go on our todo list.

I think you're asking for more scriptability in psql. Personally I
think that would be a great idea, but we need a lot more than what's
being proposed here. We'll also need loops, conditionals, etc. We've
had patches for those submitted over the years, but one at a time they
are easily rejected because they're so obviously incomplete.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Josh Berkus <josh@agliodbs.com> writes:

but you can't do
\set myvar "Select now()"

That's a seriously ridiculous syntax.

If you want something like this, a variant of \g that squirts the
results to a variable instead of a file would be more usable:
no quoting problems and no restriction to a single-line query.

regards, tom lane

I think you're asking for more scriptability in psql. Personally I
think that would be a great idea, but we need a lot more than what's
being proposed here. We'll also need loops, conditionals, etc. We've
had patches for those submitted over the years, but one at a time they
are easily rejected because they're so obviously incomplete.

"We can't do anything unless we do everything" is a recipe for failure.

...Robert

On Wed, 22 Oct 2008 19:06:59 -0400
"Robert Haas" <robertmhaas@gmail.com> wrote:

I think you're asking for more scriptability in psql. Personally I
think that would be a great idea, but we need a lot more than what's
being proposed here. We'll also need loops, conditionals, etc.
We've had patches for those submitted over the years, but one at a
time they are easily rejected because they're so obviously
incomplete.

"We can't do anything unless we do everything" is a recipe for
failure.

True enough, but a car doesn't roll without at least four wheels.

...Robert

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/

Tom,

If you want something like this, a variant of \g that squirts the
results to a variable instead of a file would be more usable:
no quoting problems and no restriction to a single-line query.

I don't care about syntax, and neither does David, I think. If we use \g
or \o, that's fine with me.

--
--Josh

Josh Berkus
PostgreSQL
San Francisco

True enough, but a car doesn't roll without at least four wheels.

True, but I'm not sure why we'd need three other wheels to make this
feature roll, or what those three wheels would be. Personally, I
would never write a complicated script in psql rather than perl, but I
can imagine using this interactively instead of cutting and pasting as
I presently do.

...Robert

On Wed, 22 Oct 2008 20:43:44 -0400
"Robert Haas" <robertmhaas@gmail.com> wrote:

True enough, but a car doesn't roll without at least four wheels.

True, but I'm not sure why we'd need three other wheels to make this
feature roll, or what those three wheels would be. Personally, I
would never write a complicated script in psql rather than perl, but I
can imagine using this interactively instead of cutting and pasting as
I presently do.

...Robert

So now that we have both agreed with each other, what do we do? :P

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/

So now that we have both agreed with each other, what do we do? :P

Well, the original suggestion was to add it to the TODO list. That
seems reasonable to me.

...Robert

Joshua Drake <jd@commandprompt.com> writes:

"Robert Haas" <robertmhaas@gmail.com> wrote:

"We can't do anything unless we do everything" is a recipe for
failure.

True enough, but a car doesn't roll without at least four wheels.

Perhaps more to the point: you don't have to implement every part of a
set of features at the same time, but it's a good idea to design them
all together. Else you risk writing something that doesn't play nice
with the other parts; and by the time you find out, it's too late to
change without creating a backwards-compatibility problem.

regards, tom lane

Alvaro Herrera <alvherre@commandprompt.com> writes:

Josh Berkus wrote:

Hackers,

Just had a feature request from Wheeler, and I don't see why it shouldn't
go on our todo list.

I think you're asking for more scriptability in psql. Personally I
think that would be a great idea, but we need a lot more than what's
being proposed here. We'll also need loops, conditionals, etc. We've
had patches for those submitted over the years, but one at a time they
are easily rejected because they're so obviously incomplete.

FWIW I disagree. I think that's a recipe for ending up with a hacky
turing-complete but awkward-as-hell language.

If you want a language to write arbitrary database driving code with loops,
conditionals, etc then I would recommend going with any of the existing fully
functional languages. You can write your scripts in perl, python, ruby, or
whatever.

If we do it ourselves we'll always have a terrible half-baked language.
Building a whole language with clean syntax and consistent semantics is a lot
of work.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

For making psql fully scriptable I would think it would be smarter to
embed a full well-supported existing language rather than trying to
invent a new one.

If we do want to to that I would suggest looking at lua which is
intended to be used this way.

greg

On 23 Oct 2008, at 05:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

On 23 okt 2008, at 00.48, Alvaro Herrera <alvherre@commandprompt.com>
wrote:

Josh Berkus wrote:

Hackers,

Just had a feature request from Wheeler, and I don't see why it
shouldn't
go on our todo list.

I think you're asking for more scriptability in psql. Personally I
think that would be a great idea, but we need a lot more than what's
being proposed here. We'll also need loops, conditionals, etc. We've
had patches for those submitted over the years, but one at a time they
are easily rejected because they're so

If you want full scripting, look at pgscript, one of this summers gsoc
projects. It'll ship in the next pgadmin and is also available as a
standalone executable.

/Magnus

2008/10/23 Greg Stark <greg.stark@enterprisedb.com>:

For making psql fully scriptable I would think it would be smarter to embed
a full well-supported existing language rather than trying to invent a new
one.

plpgsql?

regards
Pavel

Pavel Stehule wrote:

2008/10/23 Greg Stark <greg.stark@enterprisedb.com>:

For making psql fully scriptable I would think it would be smarter to embed
a full well-supported existing language rather than trying to invent a new
one.

plpgsql?

I think plpgsql is a pretty good example of the sort of disaster you get
when you invent a language from nearly scratch.

Magnus Hagander wrote:

If you want full scripting, look at pgscript, one of this summers gsoc
projects. It'll ship in the next pgadmin and is also available as a
standalone executable.

Yet another language?

What people asking for psql scriptability really want, in my estimate,
is the ability to write "SQL plus some control structures" anywhere, in
the server, in the client, or so that they don't have to know where.
Commercial vendors have that: Oracle has PL/SQL as server-side language
and client-side language. Microsoft has T-SQL. The others have similar
offerings.

What PostgreSQL has in one kind of extended SQL language in the
terminal, another kind of extended SQL language in the server (but
really two separate languages, one for functions, one outside of
functions), and now yet another kind of extended SQL language in the
GUI, each mutually wildly incompatible. And neither is based on a standard.

Something to think about ...

2008/10/23 Peter Eisentraut <peter_e@gmx.net>:

Magnus Hagander wrote:

If you want full scripting, look at pgscript, one of this summers gsoc
projects. It'll ship in the next pgadmin and is also available as a
standalone executable.

Yet another language?

it is more crazy - pgscript is based on t-sql :).

I missing in pgsql mainly #ifdef and #endif - conditional execution.

Pavel

On Wed, Oct 22, 2008 at 04:14:11PM -0700, Joshua Drake wrote:

True enough, but a car doesn't roll without at least four wheels.

I'm not sure I agree: http://en.wikipedia.org/wiki/Image:1885Benz.jpg

(Sorry, I couldn't resist.)

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Everyone,

What people asking for psql scriptability really want, in my estimate,
is the ability to write "SQL plus some control structures" anywhere, in
the server, in the client, or so that they don't have to know where.
Commercial vendors have that: Oracle has PL/SQL as server-side language
and client-side language. Microsoft has T-SQL. The others have similar
offerings.

I'm *not* proposing to implement full scriptability in psql. Just one
obvious extension of the existing pgsql variable feature.

I agree that giving psql its own unique scripting language would be
stupid. We should use something existing. Ideally, we should make psql
pluggable with the Perl & Python debuggers.

--Josh