Thread: psql Feature request \set query
Hackers, Just had a feature request from Wheeler, and I don't see why it shouldn't go on our todo list. Currently, in psql you can do: \set myvar 2008-10-23 and you can do \set myvar `date` but you can't do \set myvar "Select now()" even though you could, bizarrely, do: \set myvar `psql -c "SELECT now();"` ... provided you were willing to parse out the garbage in the output. This seems inconsistent, and it seems like the ability to store the results of a single-row-returning query in a psql variable would be useful for testing. yes/no? -- --Josh Josh Berkus PostgreSQL San Francisco
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: > 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 > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
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 > > 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: > >> 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 >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
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 > > 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 ... > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
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