# Major upgrade to ext/pgsql - Thread: https://externals.io/message/15460 - Original archive: https://news-web.php.net/php.internals/15460 - Started by Christopher Kings-Lynne on 2005-03-16T05:21:16+00:00 - Messages: 5 - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #15460 — Christopher Kings-Lynne — 2005-03-16T05:21:16+00:00 URL: https://externals.io/message/15460#15460 Hi, Attached is a patch that brings PostgreSQL support in PHP out of the stone age! It adds a five new functions: * pg_query_params Allows paramaterised queries (ie. no escaping required). This really is just a libpq shortcut for prepare/execute libpq function: PQexecParams * pg_prepare Creates a named prepared query libpq function: PQprepare * pg_execute Executes a named prepared query libpq function: PQexecPrepared * pg_transaction_status Returns transaction status of a connection libpq function: PQtransactionStatus * pg_result_error_field Allows getting advanced diagnostics on errors, most importantly allows getting the SQLSTATE error code on errors, therefore finally pgsql users can identify errors by ID. libpq function: PQresultErrorField Notes ----- I haven't attached docs or regression tests, these will follow if the patch is accepted (or before if you require) Should pg_query_params instead be folded into pg_query, with an optional 3rd parameter which would be the parameter array? Would this then be difficult for clients to detect if the 3rd parameter is available to them? I've tested it all and seems to work fine, please review :) Cheers, Chris --- ## #15465 — Edin Kadribasic — 2005-03-16T10:48:18+00:00 URL: https://externals.io/message/15460#15465 · In reply to: #15460 I wouldn't call pgsql support in PHP stone age :) I do think that this would be a very nice addition to php 5.1. A few questions: Why do we need pg_query_params. Isn't prepare/execute enough? pg_execute($conn, $stmt, $params) seem to be already taking care of the parameters passed. (btw. I wouldn't make $conn optional, it makes adding additional parameters later on pain). How does this work over persistent connections? AFAIK trying to prepare a statment with the same name on the same connection will result in an error. Edin > Hi, > > Attached is a patch that brings PostgreSQL support in PHP out of the > stone age! > It adds a five new functions: > > * pg_query_params > > Allows paramaterised queries (ie. no escaping required). This really is > just a libpq shortcut for prepare/execute > > libpq function: PQexecParams > > * pg_prepare > > Creates a named prepared query > > libpq function: PQprepare > > * pg_execute > > Executes a named prepared query > > libpq function: PQexecPrepared > > * pg_transaction_status > > Returns transaction status of a connection > > libpq function: PQtransactionStatus > > * pg_result_error_field > > Allows getting advanced diagnostics on errors, most importantly allows > getting the SQLSTATE error code on errors, therefore finally pgsql users > can identify errors by ID. > > libpq function: PQresultErrorField > > Notes > ----- > > I haven't attached docs or regression tests, these will follow if the > patch is accepted (or before if you require) > > Should pg_query_params instead be folded into pg_query, with an optional > 3rd parameter which would be the parameter array? Would this then be > difficult for clients to detect if the 3rd parameter is available to them? > > I've tested it all and seems to work fine, please review :) > > Cheers, > > Chris --- ## #15469 — Christopher Kings-Lynne — 2005-03-17T02:08:35+00:00 URL: https://externals.io/message/15460#15469 · In reply to: #15465 > I wouldn't call pgsql support in PHP stone age :) Hmmm, you're obviously not a PostgreSQL developer, and been wondering why 2 year old postgresql technology hasn't been supported yet :D > I do think that this would be a very nice addition to php 5.1. > > A few questions: > > Why do we need pg_query_params. Isn't prepare/execute enough? It's a 1-1 mapping of the libpq PostgreSQL API. Basically it allows you to avoid sql injection attacks and quoting, and also not have to worry about having to prepare/execute a named prepared query that, as you mention below, might collide with a previous one. I chose to emulate the wisdom of the libpq API designers on this one. Check here: http://www.postgresql.org/docs/current/static/libpq-exec.html > pg_execute($conn, $stmt, $params) seem to be already taking care of the > parameters passed. (btw. I wouldn't make $conn optional, it makes adding > additional parameters later on pain). I made it optional simply to match the other postgresql functions, specifically pg_query. It's easy enough to change if you like. > How does this work over persistent connections? AFAIK trying to prepare a > statment with the same name on the same connection will result in an error. Correct, but that's exactly the same as executing a PREPARE command via SQL instead of via the v3 protocol. (Which people have been doing for years.) PostgreSQL currently lacks adequate commands for properly resetting persistent connections :( For instance, prepared queries and named open cursors will persist across persistent connections in PHP - but that's not new. Chris --- ## #15470 — Christopher Kings-Lynne — 2005-03-17T02:36:32+00:00 URL: https://externals.io/message/15460#15470 · In reply to: #15465 > Why do we need pg_query_params. Isn't prepare/execute enough? > pg_execute($conn, $stmt, $params) seem to be already taking care of the > parameters passed. (btw. I wouldn't make $conn optional, it makes adding > additional parameters later on pain). Oh, if I didn't explain clearly the $stmt parameter to pg_execute is the NAME of a previous created prepared query, not the query itself. Chris --- ## #15476 — Zeev Suraski — 2005-03-17T12:30:46+00:00 URL: https://externals.io/message/15460#15476 · In reply to: #15460 At 07:21 16/03/2005, Christopher Kings-Lynne wrote: >Hi, > >Attached is a patch that brings PostgreSQL support in PHP out of the stone >age! The patch looks great. Do you have a CVS account? If not, apply for one and I'll set you up with access to ext/pgsql so that you can commit and maintain that patch. >Should pg_query_params instead be folded into pg_query, with an optional >3rd parameter which would be the parameter array? Would this then be >difficult for clients to detect if the 3rd parameter is available to them? As you noticed, right now it's a problem. Lately I've been thinking about preparing infrastructure that will allow you to specify the mandatory/optional arguments in order (as you do today), but also an extra resource-type argument, that can appear anywhere in the argument list, and will be updated if it's found or NULL'd if it isn't. This will allow the concept of 'default resource' to work better with other optional (or newly introduced) arguments. I have to think about it some more and see if it's feasible. In the meantime, you should probably keep pg_query_params(). Zeev