proposal or just idea for psql - show first N rows from relation backslash statement
Hello
probably one from my top ten SQL statement will be
SELECT * FROM some_relation LIMIT 10
what do you thinking about creating special statement for this purpose?
possible syntax
-- ViewTable
\vt table_name [rows]
or
\sample table_name [rows]
a implementation with autocomplete is terrible simple
Regards
Pavel
--
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 (pavel.stehule@gmail.com) wrote:
SELECT * FROM some_relation LIMIT 10
what do you thinking about creating special statement for this purpose?
I'd rather extend TABLE to support a limit clause or something.
Thanks,
Stephen
2013/2/13 Stephen Frost <sfrost@snowman.net>:
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
SELECT * FROM some_relation LIMIT 10
what do you thinking about creating special statement for this purpose?
I'd rather extend TABLE to support a limit clause or something.
??
Pavel
Thanks,
Stephen
--
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, February 13, 2013 21:23, Stephen Frost wrote:
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
SELECT * FROM some_relation LIMIT 10
what do you thinking about creating special statement for this purpose?
I'd rather extend TABLE to support a limit clause or something.
No need; that already does work, e.g.:
testdb=# table pg_database limit 3;
Not in the documentation, but I hope it won't get removed -- it's quite handy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Erik Rijkers (er@xs4all.nl) wrote:
No need; that already does work, e.g.:
testdb=# table pg_database limit 3;
Oh.
Not in the documentation, but I hope it won't get removed -- it's quite handy
Perhaps we should add it. :)
Thanks!
Stephen
2013/2/13 Stephen Frost <sfrost@snowman.net>:
* Erik Rijkers (er@xs4all.nl) wrote:
No need; that already does work, e.g.:
testdb=# table pg_database limit 3;
Oh.
Not in the documentation, but I hope it won't get removed -- it's quite handy
Perhaps we should add it. :)
my proposal is little but shorter
just
\vt table
and I expect so limit 10 is default
table statement is little bit different creature (and mainly it is server side)
Regards
Pavel
Thanks!
Stephen
--
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 (pavel.stehule@gmail.com) wrote:
and I expect so limit 10 is default
table statement is little bit different creature (and mainly it is server side)
I don't really see the value in this.
Thanks,
Stephen
2013/2/13 Stephen Frost <sfrost@snowman.net>:
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
and I expect so limit 10 is default
table statement is little bit different creature (and mainly it is server side)
I don't really see the value in this.
it is just shortcut for often used query - nothing more
and with larger tables you don't need to forget LIMIT clause
Regards
Pavel
Thanks,
Stephen
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Stephen Frost <sfrost@snowman.net> writes:
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
SELECT * FROM some_relation LIMIT 10
what do you thinking about creating special statement for this purpose?
I'd rather extend TABLE to support a limit clause or something.
Can't you pretty much do this already in psql with FETCH_COUNT? I see
no good reason to invent more SQL syntax.
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
2013/2/14 Tom Lane <tgl@sss.pgh.pa.us>:
Stephen Frost <sfrost@snowman.net> writes:
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
SELECT * FROM some_relation LIMIT 10
what do you thinking about creating special statement for this purpose?
I'd rather extend TABLE to support a limit clause or something.
Can't you pretty much do this already in psql with FETCH_COUNT? I see
no good reason to invent more SQL syntax.
Doesn't that just split up the retrieval of the result set into blocks of
FETCH_COUNT rows, i.e. does not limit the result set?
Personally I set commonly-used queries as a psql variable, though
what Pavel suggests sounds useful and AFAICT is not additional SQL
syntax, just (yet another) psql slash command.
Ian Barwick
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Ian Lawrence Barwick <barwick@gmail.com> writes:
2013/2/14 Tom Lane <tgl@sss.pgh.pa.us>:
Can't you pretty much do this already in psql with FETCH_COUNT? I see
no good reason to invent more SQL syntax.
Doesn't that just split up the retrieval of the result set into blocks of
FETCH_COUNT rows, i.e. does not limit the result set?
Well, you'll get a page worth of data before your pager blocks it,
and then you can continue, or not, for relatively little cost.
I can't see that fetching a fixed number of rows is more useful
than that.
[ experiments... ] Although I notice that psql's FETCH_COUNT logic
doesn't realize that it could be applied to a "TABLE foo" query.
That seems like it might be worth fixing.
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 Wed, Feb 13, 2013 at 6:07 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
Hello
probably one from my top ten SQL statement will be
SELECT * FROM some_relation LIMIT 10
what do you thinking about creating special statement for this purpose?
possible syntax
-- ViewTable
\vt table_name [rows]or
\sample table_name [rows]
a implementation with autocomplete is terrible simple
I liked this idea, but thinking better we can implement a way to users
create your own meta-commands to run:
* another meta commands (like an alias)
* SRFs
* arbitrary SQLs
All of them must accept arguments... some like this:
\mset vt :table :rows 'select * from :table limit :rows'
Then we can do exactly what you need:
\vt foo 10
\unset vt
I don't know if it's a desired feature but I would love if it exists.
Best regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Show quoted text
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Hello
I liked this idea, but thinking better we can implement a way to users
create your own meta-commands to run:* another meta commands (like an alias)
* SRFs
* arbitrary SQLsAll of them must accept arguments... some like this:
\mset vt :table :rows 'select * from :table limit :rows'
Then we can do exactly what you need:
\vt foo 10
\unset vt
I don't know if it's a desired feature but I would love if it exists.
few year ago I proposed a implementation of macros - and I wrote a
prototype - enhanced psql
http://okbob.blogspot.cz/search?q=epsql
but now I don't think so enhancing psql in this direction is good way.
Enhanced console needs creating from scratch - it should be based on
some interpret language - LUA or javascript - probably it can be
better integrated to modern UI, can better do autocomplete - and this
design is out of my time possibilities and out of my targets.
Native implementation of \vt is terrible simple - and it is generic
and usual task
check it, please
Regards
Pavel
p.s. some simple form of bash alias command can be implemented, but
probably without autocomplete support
p.s.2. inside quotes or double quotes variables substitution doesn't work
Show quoted text
Best regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQLBlog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Attachments:
vt.patchapplication/octet-stream; name=vt.patchDownload
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***************
*** 1355,1360 **** exec_command(const char *cmd,
--- 1355,1398 ----
free(opt);
}
+ /* \vt -- show sample of table */
+ else if (strcmp(cmd, "vt") == 0)
+ {
+ char *relname;
+ char *ln;
+
+ /* We don't do SQLID reduction on the pattern yet */
+ relname = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ printfPQExpBuffer(query_buf, "SELECT * FROM %s", relname);
+
+ /* try to get separate lineno arg */
+ ln = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+ if (ln != NULL)
+ {
+ int lineno = atoi(ln);
+
+ if (lineno < 1)
+ {
+ psql_error("invalid line number: %s\n", ln);
+ status = PSQL_CMD_ERROR;
+ }
+ else
+ appendPQExpBuffer(query_buf, " LIMIT %d", lineno);
+
+ pfree(ln);
+ }
+ else
+ appendPQExpBuffer(query_buf, " LIMIT 10");
+
+ if (status != PSQL_CMD_ERROR)
+ success = SendQuery(query_buf->data);
+
+ pfree(relname);
+ }
+
/* \w -- write query buffer to file */
else if (strcmp(cmd, "w") == 0 || strcmp(cmd, "write") == 0)
{
***************
*** 1430,1435 **** exec_command(const char *cmd,
--- 1468,1474 ----
free(opt);
}
+
/* \z -- list table rights (equivalent to \dp) */
else if (strcmp(cmd, "z") == 0)
{
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 869,875 **** psql_completion(char *text, int start, int end)
"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
"\\set", "\\sf", "\\t", "\\T",
! "\\timing", "\\unset", "\\x", "\\w", "\\z", "\\!", NULL
};
(void) end; /* not used */
--- 869,875 ----
"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
"\\o", "\\p", "\\password", "\\prompt", "\\pset", "\\q", "\\qecho", "\\r",
"\\set", "\\sf", "\\t", "\\T",
! "\\timing", "\\unset", "\\x", "\\vt", "\\w", "\\z", "\\!", NULL
};
(void) end; /* not used */
***************
*** 3239,3244 **** psql_completion(char *text, int start, int end)
--- 3239,3246 ----
completion_charp = "\\";
matches = completion_matches(text, complete_from_files);
}
+ else if (strncmp(prev_wd, "\\vt", strlen("\\vt")) == 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
/*
* Finally, we look through the list of "things", such as TABLE, INDEX and
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
Native implementation of \vt is terrible simple - and it is generic
and usual task
I'm still a -1 on this. We don't have anything like this today and I
don't think it's a good idea to try adding these kinds of
shortcuts-for-generic-SQL to psql's set of \ commands. Being simple to
implement doesn't make it a good idea.
Thanks,
Stephen
On Thu, Feb 14, 2013 at 1:25 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
few year ago I proposed a implementation of macros - and I wrote a
prototype - enhanced psqlhttp://okbob.blogspot.cz/search?q=epsql
but now I don't think so enhancing psql in this direction is good way.
Enhanced console needs creating from scratch - it should be based on
some interpret language - LUA or javascript - probably it can be
better integrated to modern UI, can better do autocomplete - and this
design is out of my time possibilities and out of my targets.
The main argument against psql macros II guess is that if you want
bash like features, well, use bash.
For my part I think that whenever psql enhancements are brought up the
first think to consider is 'should this functionality exist on the
server side'. With proper stored procedures we get to write our own
stuff like:
CALL top10('foo');
which seems more general and just as terse. So I think implementing
call syntax is probably topping my 'wanted feature' list.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Merlin Moncure (mmoncure@gmail.com) wrote:
CALL top10('foo');
which seems more general and just as terse. So I think implementing
call syntax is probably topping my 'wanted feature' list.
We have that, it's called 'SELECT' and it's only 2 more characters..
Thanks,
Stephen
2013/2/14 Merlin Moncure <mmoncure@gmail.com>:
On Thu, Feb 14, 2013 at 1:25 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
few year ago I proposed a implementation of macros - and I wrote a
prototype - enhanced psqlhttp://okbob.blogspot.cz/search?q=epsql
but now I don't think so enhancing psql in this direction is good way.
Enhanced console needs creating from scratch - it should be based on
some interpret language - LUA or javascript - probably it can be
better integrated to modern UI, can better do autocomplete - and this
design is out of my time possibilities and out of my targets.The main argument against psql macros II guess is that if you want
bash like features, well, use bash.For my part I think that whenever psql enhancements are brought up the
first think to consider is 'should this functionality exist on the
server side'. With proper stored procedures we get to write our own
stuff like:CALL top10('foo');
which seems more general and just as terse. So I think implementing
call syntax is probably topping my 'wanted feature' list.
My proposal should not replace stored procedures.
The core of my proposal was using autocomplete for one often task.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-02-14 09:33:51 -0500, Stephen Frost wrote:
* Merlin Moncure (mmoncure@gmail.com) wrote:
CALL top10('foo');
which seems more general and just as terse. So I think implementing
call syntax is probably topping my 'wanted feature' list.We have that, it's called 'SELECT' and it's only 2 more characters..
Well, I guess Merlin wanted actual stored procedures with full control
over transactions... Not exactly a small task.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2013/2/14 Stephen Frost <sfrost@snowman.net>:
* Merlin Moncure (mmoncure@gmail.com) wrote:
CALL top10('foo');
which seems more general and just as terse. So I think implementing
call syntax is probably topping my 'wanted feature' list.We have that, it's called 'SELECT' and it's only 2 more characters..
it is not true
SELECT * FROM foo LIMIT 10 -- 26 chars
CALL top('foo') -- 15 chars
\vt foo -- 7 chars
Regards
Pavel
Thanks,
Stephen
--
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 (pavel.stehule@gmail.com) wrote:
it is not true
It most certainly is true- did you look at the command?
SELECT top10('foo');
Note that it's "top10", implying that it'd return the first 10 records.
That's only 2 characters more than:
CALL top10('foo');
It's not as short as '\vt foo', but I never claimed that it was, nor do
I feel it's particularly valuable to shorten it down to that level.
Thanks,
Stephen
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
My proposal should not replace stored procedures.
Stored procedures, with actual transaction-management capabilities, is a
completely different topic which isn't usefully involved here.
The core of my proposal was using autocomplete for one often task.
TABLE already supports tab-completion.
Thanks,
Stephen
Pavel Stehule escribió:
2013/2/14 Stephen Frost <sfrost@snowman.net>:
* Merlin Moncure (mmoncure@gmail.com) wrote:
CALL top10('foo');
which seems more general and just as terse. So I think implementing
call syntax is probably topping my 'wanted feature' list.We have that, it's called 'SELECT' and it's only 2 more characters..
it is not true
SELECT * FROM foo LIMIT 10 -- 26 chars
CALL top('foo') -- 15 chars
\vt foo -- 7 chars
table foo limit 10; -- can be ta<tab>, saves 4 keystrokes
Needs autocompletion for "limit", then it's 8 keystrokes plus table
name, no extraneous features needed.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Feb 14, 2013 at 8:43 AM, Stephen Frost <sfrost@snowman.net> wrote:
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
My proposal should not replace stored procedures.
Stored procedures, with actual transaction-management capabilities, is a
completely different topic which isn't usefully involved here.The core of my proposal was using autocomplete for one often task.
TABLE already supports tab-completion.
TABLE is fine. My point is that often when discussing extensions to
psql are we really working around lack of stored procedures. Talk of
stored procedures is not off topic, because they could directly
implement proposed $feature in a much more general way. I am very
sanguine about implementation difficulties but please don't shoot the
messenger.
SELECT top10('foo');
That doesn't work. functions don't allow arbitrary returned columns.
CALL should support this.
Anyways, if we are counting characters,
TA<tab> fo<tab> limit 10; < 17
CA<tab> to<tab>('foo'); < 15 (but different table name could
certainly swing it)
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Merlin Moncure (mmoncure@gmail.com) wrote:
That doesn't work. functions don't allow arbitrary returned columns.
CALL should support this.
That's yet another discussion, though for a preview, you could just
return a single text column from the function in which you do whatever
formatting you want.
Thanks,
Stephen
2013/2/14 Stephen Frost <sfrost@snowman.net>:
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
it is not true
It most certainly is true- did you look at the command?
SELECT top10('foo');
Note that it's "top10", implying that it'd return the first 10 records.
That's only 2 characters more than:
In current implementation you need a a polymorphic function
and then you have to write
SELECT (top10('foo')).*
or
SELECT * FROM top10('foo')
Regards
Pavel
CALL top10('foo');
It's not as short as '\vt foo', but I never claimed that it was, nor do
I feel it's particularly valuable to shorten it down to that level.Thanks,
Stephen
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2013/2/14 Stephen Frost <sfrost@snowman.net>:
* Merlin Moncure (mmoncure@gmail.com) wrote:
That doesn't work. functions don't allow arbitrary returned columns.
CALL should support this.That's yet another discussion, though for a preview, you could just
return a single text column from the function in which you do whatever
formatting you want.
it is not easy - we have no available any formatting support on server side
so then you need to supply lot of libpq code
Pavel
Thanks,
Stephen
--
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 (pavel.stehule@gmail.com) wrote:
it is not easy - we have no available any formatting support on server side
Sure we do.
so then you need to supply lot of libpq code
No, you don't.
This discussion isn't going to change my feelings on this particular
misfeature. If others feel it's valuable and important then they can
certainly speak-up.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
This discussion isn't going to change my feelings on this particular
misfeature. If others feel it's valuable and important then they can
certainly speak-up.
I think the same --- a new backslash command for this is absolutely not
worth its weight compared to using "TABLE foo".
We have identified at least two places where psql could be improved to
support the use of "TABLE foo" better, without adding any new syntax
(viz, teach the FETCH_COUNT logic about it and improve tab-completion
to know about the possibility of limit/offset clauses). I think that's
a much more sensible path to pursue than inventing more "features".
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 Thu, Feb 14, 2013 at 10:32 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2013/2/14 Stephen Frost <sfrost@snowman.net>:
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
it is not true
It most certainly is true- did you look at the command?
SELECT top10('foo');
Note that it's "top10", implying that it'd return the first 10 records.
That's only 2 characters more than:In current implementation you need a a polymorphic function
and then you have to write
SELECT (top10('foo')).*
or
SELECT * FROM top10('foo')
that can't work either -- at least not without a column select list.
there has to be some clue to output time from arguments to the
function.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Feb 14, 2013 at 10:03 AM, Stephen Frost <sfrost@snowman.net> wrote:
* Merlin Moncure (mmoncure@gmail.com) wrote:
That doesn't work. functions don't allow arbitrary returned columns.
CALL should support this.That's yet another discussion, though for a preview, you could just
return a single text column from the function in which you do whatever
formatting you want.
sure -- I use this technique often although lately i've moved from
text to hstore/json.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers