Re: psql shows line number

Hi all,

When I execute query which contains error, the server returns error
with line number.

ERROR: relation "hoge" does not exist
LINE 35: hoge;

But when query is long, I was not sure the location where I wrote wrong SQL.
Does the psql have option which shows line number?
If no, I'm thinking to implement this feature. Thought?
I think it is useful if the psql shows line number like follows

[1]postgres=# SELECT
[2]postgres=# hoge.col1
[3]postgres=# FROM
[4]postgres=# hoge;

ERROR: column "col1" does not exist
LINE 2: col1

And If there is discussion at past about this, please let me know.

Regards,

-------
Sawada Masahiko

2013/11/26 Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>

> Hi all,
>
> When I execute query which contains error, the server returns error
> with line number.
>
> ERROR: relation "hoge" does not exist
> LINE 35: hoge;
>
> But when query is long, I was not sure the location where I wrote wrong
> SQL.
> Does the psql have option which shows line number?
> If no, I'm thinking to implement this feature. Thought?
> I think it is useful if the psql shows line number like follows
>
> [1]postgres=# SELECT
> [2]postgres=# hoge.col1
> [3]postgres=# FROM
> [4]postgres=# hoge;
>
> ERROR: column "col1" does not exist
> LINE 2: col1
>
> And If there is discussion at past about this, please let me know.
>

idea is interesting, but a proposed format is not clean (and little bit
ugly, and can be barrier for copy from terminal to editor. Now, simple
replace can be used for cleaning.

maybe we can introduce a command, that will print SQL from history with
line numbers - like \sf+

regards

Pavel

>
> Regards,
>
> -------
> Sawada Masahiko
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 11/26/13 1:05 PM, Sawada Masahiko wrote:
> When I execute query which contains error, the server returns error
> with line number.
>
> ERROR: relation "hoge" does not exist
> LINE 35: hoge;
>
> But when query is long, I was not sure the location where I wrote wrong SQL.
> Does the psql have option which shows line number?
> If no, I'm thinking to implement this feature. Thought?
> I think it is useful if the psql shows line number like follows

If the query is long, you'll likely want to \e to edit it anyway, no?
I'm sure any text editor worth $EDITOR will know how to jump to a
specific line.

Regards,
Marko Tiikkaja

On Tue, Nov 26, 2013 at 9:23 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> On 11/26/13 1:05 PM, Sawada Masahiko wrote:
>>
>> When I execute query which contains error, the server returns error
>> with line number.
>>
>> ERROR: relation "hoge" does not exist
>> LINE 35: hoge;
>>
>> But when query is long, I was not sure the location where I wrote wrong
>> SQL.
>> Does the psql have option which shows line number?
>> If no, I'm thinking to implement this feature. Thought?
>> I think it is useful if the psql shows line number like follows
>
>
> If the query is long, you'll likely want to \e to edit it anyway, no? I'm
> sure any text editor worth $EDITOR will know how to jump to a specific line.
Thanks!
¥e is also good. I didn't know this.
I thought that showing line number on psql is light and simple.
So I thought that psql can also have such feature as standard function.

> idea is interesting, but a proposed format is not clean (and little bit ugly, and
> can be barrier for copy from terminal to editor. Now, simple replace can be used for cleaning.
Thanks!
Yes, it is little ugly.
I was also thinking to fill space of line number with whitespace. likes:

[ 1]postgres=# SELECT
[ 2]postgres=# hoge.col1
:
[ 9]postgres=# FROM
[ 10]postgres=# hoge;

But I think that it is also something just isn't right.

> maybe we can introduce a command, that will print SQL from history with line numbers - like \sf+
I think that ¥sf+ shows definition of function. How should I use this?

Regards,

-------
Sawada Masahiko

2013/11/26 Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>

> On Tue, Nov 26, 2013 at 9:23 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> > On 11/26/13 1:05 PM, Sawada Masahiko wrote:
> >>
> >> When I execute query which contains error, the server returns error
> >> with line number.
> >>
> >> ERROR: relation "hoge" does not exist
> >> LINE 35: hoge;
> >>
> >> But when query is long, I was not sure the location where I wrote wrong
> >> SQL.
> >> Does the psql have option which shows line number?
> >> If no, I'm thinking to implement this feature. Thought?
> >> I think it is useful if the psql shows line number like follows
> >
> >
> > If the query is long, you'll likely want to \e to edit it anyway, no?
> I'm
> > sure any text editor worth $EDITOR will know how to jump to a specific
> line.
> Thanks!
> ¥e is also good. I didn't know this.
> I thought that showing line number on psql is light and simple.
> So I thought that psql can also have such feature as standard function.
>
>
> > idea is interesting, but a proposed format is not clean (and little bit
> ugly, and
> > can be barrier for copy from terminal to editor. Now, simple replace
> can be used for cleaning.
> Thanks!
> Yes, it is little ugly.
> I was also thinking to fill space of line number with whitespace. likes:
>
> [ 1]postgres=# SELECT
> [ 2]postgres=# hoge.col1
> :
> [ 9]postgres=# FROM
> [ 10]postgres=# hoge;
>
> But I think that it is also something just isn't right.
>
> > maybe we can introduce a command, that will print SQL from history with
> line numbers - like \sf+
> I think that ¥sf+ shows definition of function. How should I use this?
>

yes, but we can define new statement like \sq+ (Show Query)

Pavel

>
> Regards,
>
> -------
> Sawada Masahiko
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

2013/11/26 22:24 "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>:
>
>
>
>
> 2013/11/26 Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>
>>
>> On Tue, Nov 26, 2013 at 9:23 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>> > On 11/26/13 1:05 PM, Sawada Masahiko wrote:
>> >>
>> >> When I execute query which contains error, the server returns error
>> >> with line number.
>> >>
>> >> ERROR: relation "hoge" does not exist
>> >> LINE 35: hoge;
>> >>
>> >> But when query is long, I was not sure the location where I wrote
wrong
>> >> SQL.
>> >> Does the psql have option which shows line number?
>> >> If no, I'm thinking to implement this feature. Thought?
>> >> I think it is useful if the psql shows line number like follows
>> >
>> >
>> > If the query is long, you'll likely want to \e to edit it anyway,
no? I'm
>> > sure any text editor worth $EDITOR will know how to jump to a specific
line.
>> Thanks!
>> ¥e is also good. I didn't know this.
>> I thought that showing line number on psql is light and simple.
>> So I thought that psql can also have such feature as standard function.
>>
>>
>> > idea is interesting, but a proposed format is not clean (and little
bit ugly, and
>> > can be barrier for copy from terminal to editor. Now, simple replace
can be used for cleaning.
>> Thanks!
>> Yes, it is little ugly.
>> I was also thinking to fill space of line number with whitespace. likes:
>>
>> [ 1]postgres=# SELECT
>> [ 2]postgres=# hoge.col1
>> :
>> [ 9]postgres=# FROM
>> [ 10]postgres=# hoge;
>>
>> But I think that it is also something just isn't right.
>>
>> > maybe we can introduce a command, that will print SQL from history
with line numbers - like \sf+
>> I think that ¥sf+ shows definition of function. How should I use this?
>
>
> yes, but we can define new statement like \sq+ (Show Query)
>
I understood.
it looks good to me.

it is similer to searching history(i.g., just push up-arrow-key)?

Regards,

Masahiko Sawada

On 11/26/13 2:24 PM, Pavel Stehule wrote:
> yes, but we can define new statement like \sq+ (Show Query)

There's already \p; might not be too difficult to add a \p+ which would
also show the line numbers.

Regards,
Marko Tiikkaja

Marko Tiikkaja <marko(at)joh(dot)to> writes:
> On 11/26/13 2:24 PM, Pavel Stehule wrote:
>> yes, but we can define new statement like \sq+ (Show Query)

> There's already \p; might not be too difficult to add a \p+ which would
> also show the line numbers.

I don't actually see the point of this. If you're working with queries
that are long enough that counting lines is even slightly difficult,
surely you're going to use \e to correct the problem? So it seems to
me that the existing "\e nnn" facility pretty much does what's needed,
and there's no great need to clutter your regular display with line
numbers.

regards, tom lane

On Tue, Nov 26, 2013 at 9:22 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>> On 11/26/13 2:24 PM, Pavel Stehule wrote:
>>> yes, but we can define new statement like \sq+ (Show Query)
>
>> There's already \p; might not be too difficult to add a \p+ which would
>> also show the line numbers.
>
> I don't actually see the point of this. If you're working with queries
> that are long enough that counting lines is even slightly difficult,
> surely you're going to use \e to correct the problem? So it seems to
> me that the existing "\e nnn" facility pretty much does what's needed,
> and there's no great need to clutter your regular display with line
> numbers.

I agree in the sense line numbers don't help. But \e is not really a
solution to the problem. A pretty common thing for me to see (as an
artifact of my styling) in the log:

ERROR: syntax error at or near "from"
LINE 223: from
^

Not very helpful. Also good luck if your SQL is dynamically
generated. What I think would help *would* be able to optionally add
a some lines of context: then you'd at least have a shot at eyeballing
the error etc.

merlin