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?

Search Discussions

  • Pavel Stehule at Dec 5, 2010 at 10:02 am

    2010/12/5 Marc Balmer <marc@msys.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@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Magnus Hagander at Dec 5, 2010 at 10:10 am

    On Sun, Dec 5, 2010 at 10:22, 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
    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?
  • Heikki Linnakangas at Dec 5, 2010 at 10:58 am

    On 05.12.2010 12:10, Magnus Hagander wrote:
    On Sun, Dec 5, 2010 at 10:22, Marc Balmerwrote:
    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
  • Marc Balmer at Dec 5, 2010 at 11:04 am

    Am 05.12.2010 um 11:57 schrieb Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>:
    On 05.12.2010 12:10, Magnus Hagander wrote:
    On Sun, Dec 5, 2010 at 10:22, Marc Balmerwrote:
    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@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Magnus Hagander at Dec 5, 2010 at 11:08 am

    On Sun, Dec 5, 2010 at 11:57, Heikki Linnakangas wrote:
    On 05.12.2010 12:10, Magnus Hagander wrote:

    On Sun, Dec 5, 2010 at 10:22, Marc Balmerwrote:
    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.
  • Dmitriy Igrishin at Dec 5, 2010 at 11:43 am
    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@hagander.net>
    On Sun, Dec 5, 2010 at 11:57, Heikki Linnakangas
    wrote:
    On 05.12.2010 12:10, Magnus Hagander wrote:

    On Sun, Dec 5, 2010 at 10:22, Marc Balmerwrote:
    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@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers


    --
    // Dmitriy.
  • Andrew Chernow at Dec 6, 2010 at 4:20 am

    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/
  • Dmitriy Igrishin at Dec 6, 2010 at 6:23 am
    Hey Andrew,

    No, thanks. :-)

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

    2010/12/6 Andrew Chernow <ac@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/


    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers


    --
    // Dmitriy.
  • Merlin Moncure at Dec 6, 2010 at 2:43 pm

    On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander wrote:
    On Sun, Dec 5, 2010 at 10:22, 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
    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
  • Marc Balmer at Dec 6, 2010 at 2:55 pm

    Am 06.12.10 15:37, schrieb Merlin Moncure:
    On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander wrote:
    On Sun, Dec 5, 2010 at 10:22, 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
    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.
  • Merlin Moncure at Dec 6, 2010 at 3:16 pm

    On Mon, Dec 6, 2010 at 9:55 AM, Marc Balmer wrote:
    Am 06.12.10 15:37, schrieb Merlin Moncure:
    On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander wrote:
    On Sun, Dec 5, 2010 at 10:22, 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
    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
  • Kenneth Marshall at Dec 6, 2010 at 3:33 pm

    On Mon, Dec 06, 2010 at 10:14:55AM -0500, Merlin Moncure wrote:
    On Mon, Dec 6, 2010 at 9:55 AM, Marc Balmer wrote:
    Am 06.12.10 15:37, schrieb Merlin Moncure:
    On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander wrote:
    On Sun, Dec 5, 2010 at 10:22, 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
    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
  • Andrew Chernow at Dec 6, 2010 at 3:33 pm

    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/
  • Dmitriy Igrishin at Dec 6, 2010 at 4:40 pm
    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@esilo.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@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers


    --
    // Dmitriy.
  • Andrew Chernow at Dec 6, 2010 at 4:52 pm

    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/
  • Dmitriy Igrishin at Dec 6, 2010 at 5:05 pm
    2010/12/6 Andrew Chernow <ac@esilo.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.
  • Bruce Momjian at Dec 27, 2010 at 9:21 pm

    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@esilo.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@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers


    --
    // Dmitriy.
    --
    Bruce Momjian <bruce@momjian.us> http://momjian.us
    EnterpriseDB http://enterprisedb.com

    + It's impossible for everything to be true. +
  • Merlin Moncure at Dec 28, 2010 at 1:15 am

    On Mon, Dec 27, 2010 at 4:21 PM, Bruce Momjian 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
  • Andrew Chernow at Dec 28, 2010 at 3:06 am
    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/
  • Andrew Chernow at Dec 6, 2010 at 4:19 am

    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/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedDec 5, '10 at 9:52a
activeDec 28, '10 at 3:06a
posts21
users9
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase