Thread: 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

* 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

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

* 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

* 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

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

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

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

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 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.
>

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


> Best regards,
>
> --
> Fabrízio de Royes Mello
> Consultoria/Coaching PostgreSQL
>>> Blog sobre TI: http://fabriziomello.blogspot.com
>>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>>> Twitter: http://twitter.com/fabriziomello

* 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 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.

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

* 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 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.
>
> 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

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

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

* 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

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

* 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

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

* 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

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

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