Re: Suggesting a libpq addition

I am suggesting adding a function to libpq:

PGresult *PQvexec(PGconn *conn, const char *fmt, ...);

It behaves similar to PQexec, but it allows for printf style varargs and
does connection re-establishing if the connection fails (it can be
discussed if this already to much magic, maybe remove this part). It
has been carefully designed to handle memory the right way. We use this
since a long time.

What do you think?

2010/12/5 Marc Balmer <marc(at)msys(dot)ch>:
> I am suggesting adding a function to libpq:
>
> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>
> It behaves similar to PQexec, but it allows for printf style varargs and
> does connection re-establishing if the connection fails (it can be
> discussed if this already to much magic, maybe remove this part).  It
> has been carefully designed to handle memory the right way.  We use this
> since a long time.
>
> What do you think?
>

It's depend on implementation, but it can be a great security hole -
see SQL injection

Regards

Pavel Stehule

>
> --
> 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 Sun, Dec 5, 2010 at 10:22, Marc Balmer <marc(at)msys(dot)ch> wrote:
> I am suggesting adding a function to libpq:
>
> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>
> It behaves similar to PQexec, but it allows for printf style varargs and

How is that not a horrible idea, compared to using PQexecParams()? You
have to remember to do all your escaping and things manually, whereas
PQexecParams() does it automatically.

> does connection re-establishing if the connection fails (it can be
> discussed if this already to much magic, maybe remove this part).  It
> has been carefully designed to handle memory the right way.  We use this
> since a long time.

It certainly doesn't belong in a function like that - and in fact, I
think reconnection has to be handled at a different layer anyway.What
if the connection was in the middle of a transaction? Silently rolls
it back without letting the app know, since it switched to a new one?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 05.12.2010 12:10, Magnus Hagander wrote:
> On Sun, Dec 5, 2010 at 10:22, Marc Balmer<marc(at)msys(dot)ch> wrote:
>> I am suggesting adding a function to libpq:
>>
>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>>
>> It behaves similar to PQexec, but it allows for printf style varargs and
>
> How is that not a horrible idea, compared to using PQexecParams()? You
> have to remember to do all your escaping and things manually, whereas
> PQexecParams() does it automatically.

A varargs version of PQexecParams() would be handy, though. Imagine
being able to do:

PQexecVParams("SELECT * FROM mytable WHERE foo = $1 AND bar = $2",
foovar, barvar);

instead of constructing an array for the variables.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Am 05.12.2010 um 11:57 schrieb Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>:

> On 05.12.2010 12:10, Magnus Hagander wrote:
>> On Sun, Dec 5, 2010 at 10:22, Marc Balmer<marc(at)msys(dot)ch> wrote:
>>> I am suggesting adding a function to libpq:
>>>
>>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>>>
>>> It behaves similar to PQexec, but it allows for printf style varargs and
>>
>> How is that not a horrible idea, compared to using PQexecParams()? You
>> have to remember to do all your escaping and things manually, whereas
>> PQexecParams() does it automatically.
>
> A varargs version of PQexecParams() would be handy, though. Imagine being able to do:
>
> PQexecVParams("SELECT * FROM mytable WHERE foo = $1 AND bar = $2", foovar, barvar);
>
> instead of constructing an array for the variables.

yes, indeed. while the suggested implementation relies on the caller to do all escaping (a bad idea...), the ease of use of a printf-like function with the security of PQexecParam would be nice.

I'd say forget about my first suggestion, I will spend a bit more time on a better approach.
(and at the same time remove the connection reset code)

>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
> --
> 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 Sun, Dec 5, 2010 at 11:57, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 05.12.2010 12:10, Magnus Hagander wrote:
>>
>> On Sun, Dec 5, 2010 at 10:22, Marc Balmer<marc(at)msys(dot)ch>  wrote:
>>>
>>> I am suggesting adding a function to libpq:
>>>
>>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>>>
>>> It behaves similar to PQexec, but it allows for printf style varargs and
>>
>> How is that not a horrible idea, compared to using PQexecParams()? You
>> have to remember to do all your escaping and things manually, whereas
>> PQexecParams() does it automatically.
>
> A varargs version of PQexecParams() would be handy, though. Imagine being
> able to do:
>
> PQexecVParams("SELECT * FROM mytable WHERE foo = $1 AND bar = $2", foovar,
> barvar);
>
> instead of constructing an array for the variables.

I agree, that sounds a lot more useful. And if definitely needs to be
split off from the auto-reconnection stuff.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Hey hackers,

Varargs-exec is useful only when programmer calls it directly.
It is useless when libpq is used to create a more flexible high-level
library (e.g., for C++). PQexecParams (PQexecPrepared) are good
for it.

What about auto reconnect. There are PQreset already and
PG_CONNECTION_OK (_BAD) to manipulate the strategy of
reconnection. If connection became BAD how many times
suggested function will try to reconnect ?
IMO, auto-reconnection is a magic...

2010/12/5 Magnus Hagander <magnus(at)hagander(dot)net>

> On Sun, Dec 5, 2010 at 11:57, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> > On 05.12.2010 12:10, Magnus Hagander wrote:
> >>
> >> On Sun, Dec 5, 2010 at 10:22, Marc Balmer<marc(at)msys(dot)ch> wrote:
> >>>
> >>> I am suggesting adding a function to libpq:
> >>>
> >>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
> >>>
> >>> It behaves similar to PQexec, but it allows for printf style varargs
> and
> >>
> >> How is that not a horrible idea, compared to using PQexecParams()? You
> >> have to remember to do all your escaping and things manually, whereas
> >> PQexecParams() does it automatically.
> >
> > A varargs version of PQexecParams() would be handy, though. Imagine being
> > able to do:
> >
> > PQexecVParams("SELECT * FROM mytable WHERE foo = $1 AND bar = $2",
> foovar,
> > barvar);
> >
> > instead of constructing an array for the variables.
>
> I agree, that sounds a lot more useful. And if definitely needs to be
> split off from the auto-reconnection stuff.
>
>
> --
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/
>
> --
> 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
>

--
// Dmitriy.

On 12/5/2010 4:22 AM, Marc Balmer wrote:
> I am suggesting adding a function to libpq:
>
> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>
> It behaves similar to PQexec, but it allows for printf style varargs and
> does connection re-establishing if the connection fails (it can be
> discussed if this already to much magic, maybe remove this part). It
> has been carefully designed to handle memory the right way. We use this
> since a long time.
>
> What do you think?
>

I think it is a wonderful idea. Check out libpqtypes. It has a PQexecf,
PQexecvf, PQsendf and PQsendvf. But that is just the beginning....

http://libpqtypes.esilo.com
http://pgfoundry.org/projects/libpqtypes/
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

> A varargs version of PQexecParams() would be handy, though. Imagine being able
> to do:
>
> PQexecVParams("SELECT * FROM mytable WHERE foo = $1 AND bar = $2", foovar, barvar);
>
> instead of constructing an array for the variables.
>

http://libpqtypes.esilo.com/man3/PQexecf.html

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Hey Andrew,

No, thanks. :-)

And I don't think that libpq should follows it (libpqtypes).

2010/12/6 Andrew Chernow <ac(at)esilo(dot)com>

>
> A varargs version of PQexecParams() would be handy, though. Imagine being
>> able
>> to do:
>>
>> PQexecVParams("SELECT * FROM mytable WHERE foo = $1 AND bar = $2", foovar,
>> barvar);
>>
>> instead of constructing an array for the variables.
>>
>>
> http://libpqtypes.esilo.com/man3/PQexecf.html
>
>
> --
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.com/
>
>
> --
> 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
>

--
// Dmitriy.

On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Sun, Dec 5, 2010 at 10:22, Marc Balmer <marc(at)msys(dot)ch> wrote:
>> I am suggesting adding a function to libpq:
>>
>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>>
>> It behaves similar to PQexec, but it allows for printf style varargs and
>
> How is that not a horrible idea, compared to using PQexecParams()? You
> have to remember to do all your escaping and things manually, whereas
> PQexecParams() does it automatically.

It's only horrible if you stick to printf style formatting and you are
using sting techniques to inject parameters into the query. Non
parameterized queries should obviously be discouraged. However, it's
entirely possible to wrap the parameterized interfaces with vararg
interface (I should know, because we did exactly that) :-). This
gives you the best of both worlds, easy coding without sacrificing
safety. You might not remember the libpqtypes proposal, but libpq was
specifically extended with callbacks so that libpqtypes could exist
after the community determined that libpqtypes was too big of a change
to the libpq library. I think ultimately this should be revisited,
with libpqtypes going in core or something even richer...I've been
thinking for a while that postgres types should be abstracted out of
the backend into a library that both client and server depend on.

With libpqtypes, we decided to use postgres style format markers:
select PQexecf(conn, "select %int4 + %int8", an_int, a_bigint);

Everything is schema qualified, so that user types are supported (of
course, this requires implementing handling on the client).

Data routed through the binary protocol, with all the byte swapping
etc handled by the library. No escaping necessary. We also added
full support for arrays and composites, which are a nightmare to deal
with over straight libpq, and various other niceties like thread safe
error handling.

merlin

Am 06.12.10 15:37, schrieb Merlin Moncure:
> On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> On Sun, Dec 5, 2010 at 10:22, Marc Balmer <marc(at)msys(dot)ch> wrote:
>>> I am suggesting adding a function to libpq:
>>>
>>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>>>
>>> It behaves similar to PQexec, but it allows for printf style varargs and
>>
>> How is that not a horrible idea, compared to using PQexecParams()? You
>> have to remember to do all your escaping and things manually, whereas
>> PQexecParams() does it automatically.
>
> It's only horrible if you stick to printf style formatting and you are
> using sting techniques to inject parameters into the query. Non
> parameterized queries should obviously be discouraged. However, it's
> entirely possible to wrap the parameterized interfaces with vararg
> interface (I should know, because we did exactly that) :-). This
> gives you the best of both worlds, easy coding without sacrificing
> safety. You might not remember the libpqtypes proposal, but libpq was
> specifically extended with callbacks so that libpqtypes could exist
> after the community determined that libpqtypes was too big of a change
> to the libpq library. I think ultimately this should be revisited,
> with libpqtypes going in core or something even richer...I've been
> thinking for a while that postgres types should be abstracted out of
> the backend into a library that both client and server depend on.
>
> With libpqtypes, we decided to use postgres style format markers:
> select PQexecf(conn, "select %int4 + %int8", an_int, a_bigint);
>
> Everything is schema qualified, so that user types are supported (of
> course, this requires implementing handling on the client).
>
> Data routed through the binary protocol, with all the byte swapping
> etc handled by the library. No escaping necessary. We also added
> full support for arrays and composites, which are a nightmare to deal
> with over straight libpq, and various other niceties like thread safe
> error handling.

That would be a *HUGE* piece of software compared the relatively small
thing I am suggesting...

As for escaping (or not escaping) of string arguments, that can be seen
as a bug or a feature. I do not wan't automatic escaping of string
arguments in all cases, e.g. I might to construct an SQL statement with
dynamic parts "WHERE xy" or "AND a = b".

hypothetical example:

filter = "WHERE name like 'Balmer%'";
if (sort == SORT_DESC)
sort = " ORDER BY name DESCENDING";

PQvexec(conn, "SELECT name, nr, id FROM address %s%s", filter, sort);

So what I am aiming at right now is a PQvexec() function that basically
has printf() like semantics, but adds an additional token to the format
string (printf uses %s and %b to produce strings.) I am thinking of
adding %S and %B, which produce strings that are escaped.

That would be a small function, and reasonably safe. Or rather, the
safety is in the hands of the programmer.

On Mon, Dec 6, 2010 at 9:55 AM, Marc Balmer <marc(at)msys(dot)ch> wrote:
> Am 06.12.10 15:37, schrieb Merlin Moncure:
>> On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>>> On Sun, Dec 5, 2010 at 10:22, Marc Balmer <marc(at)msys(dot)ch> wrote:
>>>> I am suggesting adding a function to libpq:
>>>>
>>>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>>>>
>>>> It behaves similar to PQexec, but it allows for printf style varargs and
>>>
>>> How is that not a horrible idea, compared to using PQexecParams()? You
>>> have to remember to do all your escaping and things manually, whereas
>>> PQexecParams() does it automatically.
>>
>> It's only horrible if you stick to printf style formatting and you are
>> using sting techniques to inject parameters into the query.  Non
>> parameterized queries should obviously be discouraged.  However, it's
>> entirely possible to wrap the parameterized interfaces with vararg
>> interface (I should know, because we did exactly that) :-).  This
>> gives you the best of both worlds, easy coding without sacrificing
>> safety.  You might not remember the libpqtypes proposal, but libpq was
>> specifically extended with callbacks so that libpqtypes could exist
>> after the community determined that libpqtypes was too big of a change
>> to the libpq library.  I think ultimately this should be revisited,
>> with libpqtypes going in core or something even richer...I've been
>> thinking for a while that postgres types should be abstracted out of
>> the backend into a library that both client and server depend on.
>>
>> With libpqtypes, we decided to use postgres style format markers:
>> select PQexecf(conn, "select %int4 + %int8", an_int, a_bigint);
>>
>> Everything is schema qualified, so that user types are supported (of
>> course, this requires implementing handling on the client).
>>
>> Data routed through the binary protocol, with all the byte swapping
>> etc handled by the library.  No escaping necessary.  We also added
>> full support for arrays and composites, which are a nightmare to deal
>> with over straight libpq, and various other niceties like thread safe
>> error handling.
>
> That would be a *HUGE* piece of software compared the relatively small
> thing I am suggesting...

well, it's already written. All you would have to do is compile it.

> As for escaping (or not escaping) of string arguments, that can be seen
> as a bug or a feature.  I do not wan't automatic escaping of string
> arguments in all cases, e.g. I might to construct an SQL statement with
> dynamic parts "WHERE xy" or "AND a = b".

libpqtypes doesn't escape at all. It uses the internal parameterized
interfaces that don't require it. For particular types, like bytea
and timestamps, this much faster because we use the binary wire
format. Less load on the client and the server.

> hypothetical example:
>
> filter = "WHERE name like 'Balmer%'";
> if (sort == SORT_DESC)
>        sort = " ORDER BY name DESCENDING";
>
> PQvexec(conn, "SELECT name, nr, id FROM address %s%s", filter, sort);
>
> So what I am aiming at right now is a PQvexec() function that basically
> has printf() like semantics, but adds an additional token to the format
> string (printf uses %s and %b to produce strings.) I am thinking of
> adding %S and %B, which produce strings that are escaped.
>
> That would be a small function, and reasonably safe.  Or rather, the
> safety is in the hands of the programmer.

What you are suggesting doesn't provide a lot of value over sprintf
the query first, then exec it. You can do what you are suggesting
yourself, wrapping PQexec:

A hypothetical wrapper would be implemented something like:
va_list ap;
char buf[BUFSZ];
va_start(ap, query)
vsnprintf(buf, BUFSZ. query, ap);
va_end(ap);
return PQexec(buf);

This is a bad idea (security, escaping, performance)...we wrote a
faster, safer way to do it, with richer type support. Or you can do
it yourself.

merlin

On Mon, Dec 06, 2010 at 10:14:55AM -0500, Merlin Moncure wrote:
> On Mon, Dec 6, 2010 at 9:55 AM, Marc Balmer <marc(at)msys(dot)ch> wrote:
> > Am 06.12.10 15:37, schrieb Merlin Moncure:
> >> On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >>> On Sun, Dec 5, 2010 at 10:22, Marc Balmer <marc(at)msys(dot)ch> wrote:
> >>>> I am suggesting adding a function to libpq:
> >>>>
> >>>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
> >>>>
> >>>> It behaves similar to PQexec, but it allows for printf style varargs and
> >>>
> >>> How is that not a horrible idea, compared to using PQexecParams()? You
> >>> have to remember to do all your escaping and things manually, whereas
> >>> PQexecParams() does it automatically.
> >>
> >> It's only horrible if you stick to printf style formatting and you are
> >> using sting techniques to inject parameters into the query. ?Non
> >> parameterized queries should obviously be discouraged. ?However, it's
> >> entirely possible to wrap the parameterized interfaces with vararg
> >> interface (I should know, because we did exactly that) :-). ?This
> >> gives you the best of both worlds, easy coding without sacrificing
> >> safety. ?You might not remember the libpqtypes proposal, but libpq was
> >> specifically extended with callbacks so that libpqtypes could exist
> >> after the community determined that libpqtypes was too big of a change
> >> to the libpq library. ?I think ultimately this should be revisited,
> >> with libpqtypes going in core or something even richer...I've been
> >> thinking for a while that postgres types should be abstracted out of
> >> the backend into a library that both client and server depend on.
> >>
> >> With libpqtypes, we decided to use postgres style format markers:
> >> select PQexecf(conn, "select %int4 + %int8", an_int, a_bigint);
> >>
> >> Everything is schema qualified, so that user types are supported (of
> >> course, this requires implementing handling on the client).
> >>
> >> Data routed through the binary protocol, with all the byte swapping
> >> etc handled by the library. ?No escaping necessary. ?We also added
> >> full support for arrays and composites, which are a nightmare to deal
> >> with over straight libpq, and various other niceties like thread safe
> >> error handling.
> >
> > That would be a *HUGE* piece of software compared the relatively small
> > thing I am suggesting...
>
> well, it's already written. All you would have to do is compile it.
>
> > As for escaping (or not escaping) of string arguments, that can be seen
> > as a bug or a feature. ?I do not wan't automatic escaping of string
> > arguments in all cases, e.g. I might to construct an SQL statement with
> > dynamic parts "WHERE xy" or "AND a = b".
>
> libpqtypes doesn't escape at all. It uses the internal parameterized
> interfaces that don't require it. For particular types, like bytea
> and timestamps, this much faster because we use the binary wire
> format. Less load on the client and the server.
>
> > hypothetical example:
> >
> > filter = "WHERE name like 'Balmer%'";
> > if (sort == SORT_DESC)
> > ? ? ? ?sort = " ORDER BY name DESCENDING";
> >
> > PQvexec(conn, "SELECT name, nr, id FROM address %s%s", filter, sort);
> >
> > So what I am aiming at right now is a PQvexec() function that basically
> > has printf() like semantics, but adds an additional token to the format
> > string (printf uses %s and %b to produce strings.) I am thinking of
> > adding %S and %B, which produce strings that are escaped.
> >
> > That would be a small function, and reasonably safe. ?Or rather, the
> > safety is in the hands of the programmer.
>
> What you are suggesting doesn't provide a lot of value over sprintf
> the query first, then exec it. You can do what you are suggesting
> yourself, wrapping PQexec:
>
> A hypothetical wrapper would be implemented something like:
> va_list ap;
> char buf[BUFSZ];
> va_start(ap, query)
> vsnprintf(buf, BUFSZ. query, ap);
> va_end(ap);
> return PQexec(buf);
>
> This is a bad idea (security, escaping, performance)...we wrote a
> faster, safer way to do it, with richer type support. Or you can do
> it yourself.
>
> merlin
>

I have used the libpqtypes library and it is very easy to use.

+1 for adding it or something like it to the PostgreSQL core.
I have people who will try and roll their own because it does
not come with the core. While it is a hoot to see what reinventing
the wheel produces, it is also prone to mistakes.

Regards,
Ken

>
> That would be a *HUGE* piece of software compared the relatively small
> thing I am suggesting...
>

Sometimes complex and large solutions are required for the simplest of
ideas. I believe this is one of those cases. You can't solve the
"printf style PQexec" properly by merely implementing a sprintf wrapper.

> As for escaping (or not escaping) of string arguments, that can be seen
> as a bug or a feature. I do not wan't automatic escaping of string
> arguments in all cases, e.g. I might to construct an SQL statement with
> dynamic parts "WHERE xy" or "AND a = b".
>
> hypothetical example:
>
> filter = "WHERE name like 'Balmer%'";
> if (sort == SORT_DESC)
> sort = " ORDER BY name DESCENDING";
>
> PQvexec(conn, "SELECT name, nr, id FROM address %s%s", filter, sort);
>
> So what I am aiming at right now is a PQvexec() function that basically
> has printf() like semantics, but adds an additional token to the format
> string (printf uses %s and %b to produce strings.) I am thinking of
> adding %S and %B, which produce strings that are escaped.
>

This suffers from becoming cryptic over time, see Tom Lane's comments
back in 2007 on this
(http://archives.postgresql.org/pgsql-hackers/2007-12/msg00362.php).
libpqtypes uses the human readable %schema.typename (schema is optional)
to specify format specifiers. There is no learning curve or ambiguity,
if you want a point than use "%point", or "%my_type".... libpqtypes
allows you to register aliases (PQregisterSubClasses) so that you can
map %text to %s to make it feel more like C..

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

IMO, it would be better to implement some utility functions to
make it easy to construct arrays dynamically for PQexecParams
and PQexecPrepared. This seems to me more universal solution
and it is useful for both -- high level libpq-libraries authors and for
those who like to use libpq directly.

2010/12/6 Andrew Chernow <ac(at)esilo(dot)com>

>
>
>> That would be a *HUGE* piece of software compared the relatively small
>> thing I am suggesting...
>>
>>
> Sometimes complex and large solutions are required for the simplest of
> ideas. I believe this is one of those cases. You can't solve the "printf
> style PQexec" properly by merely implementing a sprintf wrapper.
>
>
> As for escaping (or not escaping) of string arguments, that can be seen
>> as a bug or a feature. I do not wan't automatic escaping of string
>> arguments in all cases, e.g. I might to construct an SQL statement with
>> dynamic parts "WHERE xy" or "AND a = b".
>>
>> hypothetical example:
>>
>> filter = "WHERE name like 'Balmer%'";
>> if (sort == SORT_DESC)
>> sort = " ORDER BY name DESCENDING";
>>
>> PQvexec(conn, "SELECT name, nr, id FROM address %s%s", filter, sort);
>>
>> So what I am aiming at right now is a PQvexec() function that basically
>> has printf() like semantics, but adds an additional token to the format
>> string (printf uses %s and %b to produce strings.) I am thinking of
>> adding %S and %B, which produce strings that are escaped.
>>
>>
> This suffers from becoming cryptic over time, see Tom Lane's comments back
> in 2007 on this (
> http://archives.postgresql.org/pgsql-hackers/2007-12/msg00362.php).
> libpqtypes uses the human readable %schema.typename (schema is optional) to
> specify format specifiers. There is no learning curve or ambiguity, if you
> want a point than use "%point", or "%my_type".... libpqtypes allows you to
> register aliases (PQregisterSubClasses) so that you can map %text to %s to
> make it feel more like C..
>
>
> --
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.com/
>
> --
> 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
>

--
// Dmitriy.

On 12/6/2010 11:40 AM, Dmitriy Igrishin wrote:
> IMO, it would be better to implement some utility functions to
> make it easy to construct arrays dynamically for PQexecParams
> and PQexecPrepared. This seems to me more universal solution
> and it is useful for both -- high level libpq-libraries authors and for
> those who like to use libpq directly.
>

Hmm, your idea isn't better, it is identical to what libpqtypes already
does :)
http://libpqtypes.esilo.com/browse_source.html?file=exec.c

We wrap PQexecParams and friends. You are coding libpq. We extended
much effort to provide the same result interface (PGresult), including
handling composites and arrays. You getf composites and arrays as
PGresults; where a composite is a single tuple multiple field result, an
array is a multiple tuple single field result and composite arrays are
multiple tuples and multiple fields. We've just made a more formal set
of utility functions, typically called an API, in an attempt to match
the coding standards of the postgresql project.

There is no libpq param interface like results, so we added PGparam
stuff. This allows you to pack parameters (PQputf) and than execute it.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

2010/12/6 Andrew Chernow <ac(at)esilo(dot)com>

> On 12/6/2010 11:40 AM, Dmitriy Igrishin wrote:
>
>> IMO, it would be better to implement some utility functions to
>> make it easy to construct arrays dynamically for PQexecParams
>> and PQexecPrepared. This seems to me more universal solution
>> and it is useful for both -- high level libpq-libraries authors and for
>> those who like to use libpq directly.
>>
>>
> Hmm, your idea isn't better, it is identical to what libpqtypes already
> does :)
> http://libpqtypes.esilo.com/browse_source.html?file=exec.c
>
Actually I don't need this functionality :-). I've implemented a library on
C++
which does many things, including auto memory management, type
conversion and binary transfers easy...
But I believe, that including proposed utility functions are better than
printf-like addition... Although, both of these a excess.

> We wrap PQexecParams and friends. You are coding libpq. We extended much
> effort to provide the same result interface (PGresult), including handling
> composites and arrays. You getf composites and arrays as PGresults; where a
> composite is a single tuple multiple field result, an array is a multiple
> tuple single field result and composite arrays are multiple tuples and
> multiple fields. We've just made a more formal set of utility functions,
> typically called an API, in an attempt to match the coding standards of the
> postgresql project.
>
There is no libpq param interface like results, so we added PGparam stuff.
> This allows you to pack parameters (PQputf) and than execute it.

So, let libpq will not be bloated. Let libpq remain low-level library for
projects like libpqtypes, pqxx and so on (my library too) ;-)

>
>
> --
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.com/
>

--
// Dmitriy.

Dmitriy Igrishin wrote:
> IMO, it would be better to implement some utility functions to
> make it easy to construct arrays dynamically for PQexecParams
> and PQexecPrepared. This seems to me more universal solution
> and it is useful for both -- high level libpq-libraries authors and for
> those who like to use libpq directly.

Is there a TODO here?

---------------------------------------------------------------------------

>
> 2010/12/6 Andrew Chernow <ac(at)esilo(dot)com>
>
> >
> >
> >> That would be a *HUGE* piece of software compared the relatively small
> >> thing I am suggesting...
> >>
> >>
> > Sometimes complex and large solutions are required for the simplest of
> > ideas. I believe this is one of those cases. You can't solve the "printf
> > style PQexec" properly by merely implementing a sprintf wrapper.
> >
> >
> > As for escaping (or not escaping) of string arguments, that can be seen
> >> as a bug or a feature. I do not wan't automatic escaping of string
> >> arguments in all cases, e.g. I might to construct an SQL statement with
> >> dynamic parts "WHERE xy" or "AND a = b".
> >>
> >> hypothetical example:
> >>
> >> filter = "WHERE name like 'Balmer%'";
> >> if (sort == SORT_DESC)
> >> sort = " ORDER BY name DESCENDING";
> >>
> >> PQvexec(conn, "SELECT name, nr, id FROM address %s%s", filter, sort);
> >>
> >> So what I am aiming at right now is a PQvexec() function that basically
> >> has printf() like semantics, but adds an additional token to the format
> >> string (printf uses %s and %b to produce strings.) I am thinking of
> >> adding %S and %B, which produce strings that are escaped.
> >>
> >>
> > This suffers from becoming cryptic over time, see Tom Lane's comments back
> > in 2007 on this (
> > http://archives.postgresql.org/pgsql-hackers/2007-12/msg00362.php).
> > libpqtypes uses the human readable %schema.typename (schema is optional) to
> > specify format specifiers. There is no learning curve or ambiguity, if you
> > want a point than use "%point", or "%my_type".... libpqtypes allows you to
> > register aliases (PQregisterSubClasses) so that you can map %text to %s to
> > make it feel more like C..
> >
> >
> > --
> > Andrew Chernow
> > eSilo, LLC
> > every bit counts
> > http://www.esilo.com/
> >
> > --
> > 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
> >
>
>
>
> --
> // Dmitriy.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Mon, Dec 27, 2010 at 4:21 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Dmitriy Igrishin wrote:
>> IMO, it would be better to implement some utility functions to
>> make it easy to construct arrays dynamically for PQexecParams
>> and PQexecPrepared. This seems to me more universal solution
>> and it is useful for both -- high level libpq-libraries authors and for
>> those who like to use libpq directly.
>
> Is there a TODO here?

As stated upthread, this has already been done in a very robust
fashion, aka libpqtypes (for example, review
http://libpqtypes.esilo.com/man3/PQexecf.html and put it in context of
the OP's request). libpq was specifically amended to support
libpqtypes as a compromise solution after it was determined that it
was too complex and controversial to merit review for inclusion into
the core project. IMNSHO, if there is a TODO, it should probably be
to consider libpqtypes for contrib (which we don't have time for atm).

merlin

> to consider libpqtypes for contrib (which we don't have time for atm).

... or as a libpq sibling :)

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/