Thread: Add \i option to bring in the specified file as a quoted literal
Hi, I would like to implement item from TODO marked as easy: "Add \i option to bring in the specified file as a quoted literal". I understand intent of this item, to be able to have parts of query written in separate files (now it is impossible, because \i tries to execute content of file as a separate command by function process_file). Implementation: I will add command "iq" and "include_quoted" in src/bin/psql/command.c (in the same block where "i" and "ir" are implemented). If "iq" is detected I will call new function do_append(fname, query_buf) which will read file into query_buffer and return status PSQL_CMD_NEWEDIT. User perespective: User will be able to append file to current buffer. It will be possible to include code of procedure, or where part of select statement. ex: select * from /iq where_part.sql and lang_name='SQL' ; Questions: Variables like :myvar will be replaced with their values, is that ok? Shall I start to code this patch? Best regards Piotr Marcinczyk
On Tue, Oct 22, 2013 at 3:04 AM, Piotr Marcinczyk <pmarcinc@gmail.com> wrote: > Hi, > > I would like to implement item from TODO marked as easy: "Add \i option > to bring in the specified file as a quoted literal". I understand intent > of this item, to be able to have parts of query written in separate > files (now it is impossible, because \i tries to execute content of file > as a separate command by function process_file). For the usecase discussed in the mail chain of that TODO item, Robert Haas has provided an alternative to achieve it, please check below link: http://www.postgresql.org/message-id/AANLkTi=7C8xFYF7uQW0y+si8oNdKoY2NX8jc4bU0GWvY@mail.gmail.com If you think that alternative is not sufficient for the use case, then adding new option/syntax is worth, otherwise it might be a shortcut or other form of some existing way which can be useful depending on how frequently users use this syntax. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
On Wed, Oct 23, 2013 at 10:31:39AM +0530, Amit Kapila wrote: > On Tue, Oct 22, 2013 at 3:04 AM, Piotr Marcinczyk <pmarcinc@gmail.com> wrote: > > Hi, > > > > I would like to implement item from TODO marked as easy: "Add \i option > > to bring in the specified file as a quoted literal". I understand intent > > of this item, to be able to have parts of query written in separate > > files (now it is impossible, because \i tries to execute content of file > > as a separate command by function process_file). > > For the usecase discussed in the mail chain of that TODO item, Robert > Haas has provided an alternative to achieve it, please check below > link: > http://www.postgresql.org/message-id/AANLkTi=7C8xFYF7uQW0y+si8oNdKoY2NX8jc4bU0GWvY@mail.gmail.com > > If you think that alternative is not sufficient for the use case, then > adding new option/syntax is worth, otherwise it might be a shortcut or > other form of some existing way which can be useful depending on how > frequently users use this syntax. So, can we remove this TODO item? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Tue, Nov 12, 2013 at 9:37 PM, Bruce Momjian <bruce@momjian.us> wrote: > On Wed, Oct 23, 2013 at 10:31:39AM +0530, Amit Kapila wrote: >> On Tue, Oct 22, 2013 at 3:04 AM, Piotr Marcinczyk <pmarcinc@gmail.com> wrote: >> > Hi, >> > >> > I would like to implement item from TODO marked as easy: "Add \i option >> > to bring in the specified file as a quoted literal". I understand intent >> > of this item, to be able to have parts of query written in separate >> > files (now it is impossible, because \i tries to execute content of file >> > as a separate command by function process_file). >> >> For the usecase discussed in the mail chain of that TODO item, Robert >> Haas has provided an alternative to achieve it, please check below >> link: >> http://www.postgresql.org/message-id/AANLkTi=7C8xFYF7uQW0y+si8oNdKoY2NX8jc4bU0GWvY@mail.gmail.com >> >> If you think that alternative is not sufficient for the use case, then >> adding new option/syntax is worth, otherwise it might be a shortcut or >> other form of some existing way which can be useful depending on how >> frequently users use this syntax. > > So, can we remove this TODO item? TODO item is created before Robert Haas has provided an alternative way to achieve the same thing. In some cases there are multiple ways to achieve the same thing (example: shortcut options in psql) if it is used quite frequently and people want some easy way of doing it. In this case I don't think this is used frequently, so I don't see the need of doing it. We should remove this TODO item. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
On Wed, Nov 13, 2013 at 08:58:07AM +0530, Amit Kapila wrote: > On Tue, Nov 12, 2013 at 9:37 PM, Bruce Momjian <bruce@momjian.us> wrote: > > On Wed, Oct 23, 2013 at 10:31:39AM +0530, Amit Kapila wrote: > >> On Tue, Oct 22, 2013 at 3:04 AM, Piotr Marcinczyk <pmarcinc@gmail.com> wrote: > >> > Hi, > >> > > >> > I would like to implement item from TODO marked as easy: "Add \i option > >> > to bring in the specified file as a quoted literal". I understand intent > >> > of this item, to be able to have parts of query written in separate > >> > files (now it is impossible, because \i tries to execute content of file > >> > as a separate command by function process_file). > >> > >> For the usecase discussed in the mail chain of that TODO item, Robert > >> Haas has provided an alternative to achieve it, please check below > >> link: > >> http://www.postgresql.org/message-id/AANLkTi=7C8xFYF7uQW0y+si8oNdKoY2NX8jc4bU0GWvY@mail.gmail.com > >> > >> If you think that alternative is not sufficient for the use case, then > >> adding new option/syntax is worth, otherwise it might be a shortcut or > >> other form of some existing way which can be useful depending on how > >> frequently users use this syntax. > > > > So, can we remove this TODO item? > TODO item is created before Robert Haas has provided an alternative > way to achieve the same thing. In some cases there are multiple ways > to > achieve the same thing (example: shortcut options in psql) if it is > used quite frequently and people want some easy way of doing it. In > this case I > don't think this is used frequently, so I don't see the need of > doing it. We should remove this TODO item. OK, removed. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Dnia 2013-11-13, śro o godzinie 10:26 -0500, Bruce Momjian pisze: > On Wed, Nov 13, 2013 at 08:58:07AM +0530, Amit Kapila wrote: > > On Tue, Nov 12, 2013 at 9:37 PM, Bruce Momjian <bruce@momjian.us> wrote: > > > On Wed, Oct 23, 2013 at 10:31:39AM +0530, Amit Kapila wrote: > > >> On Tue, Oct 22, 2013 at 3:04 AM, Piotr Marcinczyk <pmarcinc@gmail.com> wrote: > > >> > Hi, > > >> > > > >> > I would like to implement item from TODO marked as easy: "Add \i option > > >> > to bring in the specified file as a quoted literal". I understand intent > > >> > of this item, to be able to have parts of query written in separate > > >> > files (now it is impossible, because \i tries to execute content of file > > >> > as a separate command by function process_file). > > >> > > >> For the usecase discussed in the mail chain of that TODO item, Robert > > >> Haas has provided an alternative to achieve it, please check below > > >> link: > > >> http://www.postgresql.org/message-id/AANLkTi=7C8xFYF7uQW0y+si8oNdKoY2NX8jc4bU0GWvY@mail.gmail.com > > >> > > >> If you think that alternative is not sufficient for the use case, then > > >> adding new option/syntax is worth, otherwise it might be a shortcut or > > >> other form of some existing way which can be useful depending on how > > >> frequently users use this syntax. > > > > > > So, can we remove this TODO item? > > TODO item is created before Robert Haas has provided an alternative > > way to achieve the same thing. In some cases there are multiple ways > > to > > achieve the same thing (example: shortcut options in psql) if it is > > used quite frequently and people want some easy way of doing it. In > > this case I > > don't think this is used frequently, so I don't see the need of > > doing it. We should remove this TODO item. > > OK, removed. > Well, I wrote it few days ago. I'm sure it is not critical, but I suppose it may be useful. This is my first patch, so I think that it is good idea to sent it and have it reviewed anyway. First argument to send it, is to see what kind of errors I made, to not do them in the next patches. Second, if it (flexible appending file to buffer) appears interesting for reviewer, it may be committed.
Attachment
Piotr Marcinczyk escribió: > <varlistentry> > + <term><literal>\ib <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">quote_string</replaceable>] </literal></term> > + <listitem> > + <para> > + The <literal>\ib</> command appends content of file <literal>filename</literal> > + to current query buffer. If parameter <literal>quote_string</literal> > + is not set, no quotation is used. If it is set, content of file will be > + quoted by <literal>quote_string</literal> enclosed in <literal>$</literal>. > + </para> > + </listitem> > + </varlistentry> Doesn't this quoting thing seem like a usability problem? I mean, there's no way you can possibly know what string to use unless you first verify the contents of the file yourself. I think this is something that should be done automatically by psql. But, really, having to read stuff and transform into a quoted literal seems wrong to me. I would like something that would read into a client variable that can later be used as a positional parameter to a parametrized query, so \ib homer ~/photos/homer.jpg insert into people (name, photo) values ('Homer', :homer); and have psql turn that into PQexecParams("insert into people (name, photo) values ('homer', $1)", some_array_with_homer); so that no quoting needs to happen anywhere. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Nov 22, 2013 at 1:33 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Piotr Marcinczyk escribió: > >> <varlistentry> >> + <term><literal>\ib <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">quote_string</replaceable>] </literal></term> >> + <listitem> >> + <para> >> + The <literal>\ib</> command appends content of file <literal>filename</literal> >> + to current query buffer. If parameter <literal>quote_string</literal> >> + is not set, no quotation is used. If it is set, content of file will be >> + quoted by <literal>quote_string</literal> enclosed in <literal>$</literal>. >> + </para> >> + </listitem> >> + </varlistentry> > > Doesn't this quoting thing seem like a usability problem? I mean, > there's no way you can possibly know what string to use unless you first > verify the contents of the file yourself. I think this is something > that should be done automatically by psql. > > But, really, having to read stuff and transform into a quoted literal > seems wrong to me. I would like something that would read into a client > variable that can later be used as a positional parameter to a > parametrized query, so > > \ib homer ~/photos/homer.jpg > insert into people (name, photo) values ('Homer', :homer); Isn't something similar already supported as mentioned in docs: One example use of this mechanism is to copy the contents of a file into a table column. First load the file into a variable and then interpolate the variable's value as a quoted string: testdb=> \set content `cat my_file.txt` testdb=> INSERT INTO my_table VALUES (:'content'); or do you prefer an alternative without any kind of quote using \ib? With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Amit Kapila escribió: > On Fri, Nov 22, 2013 at 1:33 AM, Alvaro Herrera > <alvherre@2ndquadrant.com> wrote: > > \ib homer ~/photos/homer.jpg > > insert into people (name, photo) values ('Homer', :homer); > > Isn't something similar already supported as mentioned in docs: > > One example use of this mechanism is to copy the contents of a file > into a table column. First load the file into a variable and then > interpolate the variable's value as a quoted string: > > testdb=> \set content `cat my_file.txt` > testdb=> INSERT INTO my_table VALUES (:'content'); > > or do you prefer an alternative without any kind of quote using \ib? If the only use case of the feature proposed in this thread is to load stuff from files to use as column values, then we're pretty much done, and this patch is not needed -- except, maybe, that the `` is unlikely to work on Windows, as already mentioned elsewhere. But if the OP had something else in mind, let's hear what it is. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Fri, 2013-11-22 at 09:54 -0300, Alvaro Herrera wrote: > Amit Kapila escribió: > > On Fri, Nov 22, 2013 at 1:33 AM, Alvaro Herrera > > <alvherre@2ndquadrant.com> wrote: > > > > \ib homer ~/photos/homer.jpg > > > insert into people (name, photo) values ('Homer', :homer); > > > > Isn't something similar already supported as mentioned in docs: > > > > One example use of this mechanism is to copy the contents of a file > > into a table column. First load the file into a variable and then > > interpolate the variable's value as a quoted string: > > > > testdb=> \set content `cat my_file.txt` > > testdb=> INSERT INTO my_table VALUES (:'content'); > > > > or do you prefer an alternative without any kind of quote using \ib? > > If the only use case of the feature proposed in this thread is to load > stuff from files to use as column values, then we're pretty much done, > and this patch is not needed -- except, maybe, that the `` is unlikely > to work on Windows, as already mentioned elsewhere. But if the OP had > something else in mind, let's hear what it is. > I've test set command mentioned above, and I think it is enough. At this moment I see no point in implementing new command. Best Regards Piotr Marcinczyk