Thread: Does PG support bulk operation in embedded C
To explain pls refer to this for DB2 http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html Essentially in one single sql call, we can do -- Add new rows -- Update a set of rows where each row is identified by a bookmark -- Delete a set of rows where each row is identified by a bookmark -- Fetch a set of rows where each row is identified by a bookmark This gives tremendous performance benefits as the network round trip is avoided for each sql. I am looking for an equivalent of this in PG and C language. Thanks.
On 05/19/2015 04:47 PM, Ravi Krishna wrote: > > To explain pls refer to this for DB2 > > http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html > > > Essentially in one single sql call, we can do > -- Add new rows > -- Update a set of rows where each row is identified by a bookmark > -- Delete a set of rows where each row is identified by a bookmark > -- Fetch a set of rows where each row is identified by a bookmark > > This gives tremendous performance benefits as the network round trip is > avoided for each sql. > > I am looking for an equivalent of this in PG and C language. For embedded C, I believe you are looking for: http://www.postgresql.org/docs/9.4/static/ecpg.html > > Thanks. > > > -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
<div style="font-family: Verdana;font-size: 12.0px;"><div><div>Not sure whether I am understanding this. I checked embeddedC and did not find any section which describes what I have asked, that is the ability to do multiple inserts, orupdates or deletes in one sql call. For example, if my application does the following<br /><br /> BEGIN TRANSACTION<br/> INSERT INTO TABLE_A<br /> UPDATE TABLE_B<br /> INSERT INTO TABLE_C</div><div>COMMIT TRANSACTION</div><div><br/> DB2 provides to combine the three sql operations into an array and make a call to DB2 which executesthe array (that is all 3 sqls as one single call).<br /><br /> I am looking for something similar in PG.<br /><br/> thanks</div><div> <div name="quote" style="margin: 10px 5px 5px 10px; padding: 10px 0px 10px 10px; border-left-color:rgb(195, 217, 229); border-left-width: 2px; border-left-style: solid; -ms-word-wrap: break-word; -webkit-nbsp-mode:space; -webkit-line-break: after-white-space;"><div style="margin: 0px 0px 10px;"><b>Sent:</b> Tuesday,May 19, 2015 at 8:13 PM<br /><b>From:</b> "Joshua D. Drake" <jd@commandprompt.com><br /><b>To:</b> "RaviKrishna" <srkrishna@gmx.com>, pgsql-sql@postgresql.org<br /><b>Cc:</b> pgsql-general@postgresql.org<br/><b>Subject:</b> Re: [SQL] [GENERAL] Does PG support bulk operation in embeddedC</div><div name="quoted-content"><br /> On 05/19/2015 04:47 PM, Ravi Krishna wrote:<br /> ><br /> > To explainpls refer to this for DB2<br /> ><br /> > <a href="http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html" target="_blank">http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html</a><br />><br /> ><br /> > Essentially in one single sql call, we can do<br /> > -- Add new rows<br /> > -- Updatea set of rows where each row is identified by a bookmark<br /> > -- Delete a set of rows where each row is identifiedby a bookmark<br /> > -- Fetch a set of rows where each row is identified by a bookmark<br /> ><br /> >This gives tremendous performance benefits as the network round trip is<br /> > avoided for each sql.<br /> ><br/> > I am looking for an equivalent of this in PG and C language.<br /><br /> For embedded C, I believe you arelooking for:<br /><br /><a href="http://www.postgresql.org/docs/9.4/static/ecpg.html" target="_blank">http://www.postgresql.org/docs/9.4/static/ecpg.html</a><br/><br /><br /> ><br /> > Thanks.<br /> ><br/> ><br /> ><br /><br /><br /> --<br /> Command Prompt, Inc. - <a href="http://www.commandprompt.com/" target="_blank">http://www.commandprompt.com/</a>503-667-4564<br /> PostgreSQL Centered full stack support, consulting anddevelopment.<br /> Announcing "I'm offended" is basically telling the world you can't<br /> control your own emotions,so everyone else should do it for you.<br /><br /><br /> --<br /> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a></div></div></div></div></div>
On 05/19/2015 05:27 PM, Ravi Krishna wrote: > Not sure whether I am understanding this. I checked embedded C and did > not find any section which describes what I have asked, that is the > ability to do multiple inserts, or updates or deletes in one sql call. > For example, if my application does the following > > BEGIN TRANSACTION > INSERT INTO TABLE_A > UPDATE TABLE_B > INSERT INTO TABLE_C > COMMIT TRANSACTION Well PostgreSQL certainly supports the above. > > DB2 provides to combine the three sql operations into an array and make > a call to DB2 which executes the array (that is all 3 sqls as one single > call). You can do this with inserts using multivalue. INSERT INTO TABLE_A VALUES (), (), (); I am not sure about UPDATE or DELETE, I know you can use WITH on DELETE which gives you some flexibility. I don't think you will get a one to one comparison but you should be able to get close. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
On Wed, May 20, 2015 at 8:47 AM, Ravi Krishna <srkrishna@gmx.com> wrote: > Essentially in one single sql call, we can do > -- Add new rows > -- Update a set of rows where each row is identified by a bookmark > -- Delete a set of rows where each row is identified by a bookmark > -- Fetch a set of rows where each row is identified by a bookmark > > This gives tremendous performance benefits as the network round trip is > avoided for each sql. > > I am looking for an equivalent of this in PG and C language. What you are looking at could be accomplished with a user-defined function: http://www.postgresql.org/docs/devel/static/xfunc.html Perhaps you are looking for something in C, now it would be less complex to do it for example with pl/pgsql or another language, and call it from a C client with a correct set of arguments satisfying your needs. -- Michael