Thread: Simple search question
Hi,after running a script which performs an insert, a new tuple is created, and a serial number is generated for it. I want to write the new tuple data back to the screen, including the new serial number.My question is, do I have to do a search for the tuplejust inserted in order to get the data back again? I am thinking there must be a faster, more efficient way. Thanks, Alex
On Tue, 13 Jun 2000 09:42:01 +1000, Alex <gliathit@ihug.com.au> wrote: ]>Hi, ]> after running a script which performs an insert, a new tuple is ]>created, and a serial number is generated for it. I want to write the ]>new tuple data back to the screen, including the new serial number. ]> My question is, do I have to do a search for the tuple just inserted in ]>order to get the data back again? I am thinking there must be a faster, ]>more efficient way. ]>Thanks, ]>Alex What language is your "script" written in? Based on some previous posts, I guess you're talking about a PHP script. How did you add the tuple? I'd guess with a pg_Exec(connect_id,"INSERT ...."). From reading the doc, I think that you can get the row just inserted by using the pg_fetch_row() function, passing it the result from the pg_Exec and asking for row 0. I have not yet gotten anything running with PHP (lack of time to "play"), so I can't test this. If it doesn't work, I'd try using pg_GetLastOid() to get the OID of the inserted row. The use the pg_Exec and SELECT * WHERE OID=oid-value, followed by pg_fetch_row(). Just some thoughts. John
Hi, > What language is your "script" written in? Based on some previous posts, > I guess you're talking about a PHP script. How did you add the tuple? > I'd guess with a pg_Exec(connect_id,"INSERT ...."). From reading the doc, > I think that you can get the row just inserted by using the pg_fetch_row() > function, passing it the result from the pg_Exec and asking for row 0. OK, I just tried pg_fetch_array() and the above didn't work. > I have not yet gotten anything running with PHP (lack of time to "play"), For someone who hasn't had time to play, you certainly know alot about php :) If you need any examples, I'd be willing to post them, time permitting. I thought libpq was easy to use, this is very straightforward. I have been 'playing' with it for a few days, and it is my development tool of choice now. Of course, this is because of faster development time, don't expect Yahoo! to replace their cgi with PHP just yet. > so I can't test this. If it doesn't work, I'd try using pg_GetLastOid() > to get the OID of the inserted row. The use the pg_Exec and SELECT > * WHERE OID=oid-value, followed by pg_fetch_row(). Thanks John, and the other person that replied to my email (I know it is a bit of a stupid question, but in such an unpopulated list, I don't think there's any reason to post privately).This leads to another question. If someone adds another row during this, what will happen? Thanks, Alex
Judging by the PHP Manual -- http://www.php.net/manual/function.pg-getlastoid.php -- it appears that the pg_GetLastOid command returns the last OID for the given result, and therefore would be unaffected by other inserted records. From the manual: int pg_getlastoid (int result_id) -- can be used to retrieve the Oid assigned to an inserted tuple if the result identifier is used from the last command sent via pg_Exec() and was an SQL INSERT. This function will return a positive integer if there was a valid Oid. It will return -1 if an error occured or the last command sent via pg_Exec() was not an INSERT. Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 13 Jun 2000, Alex wrote: > Hi, > > > What language is your "script" written in? Based on some previous posts, > > I guess you're talking about a PHP script. How did you add the tuple? > > I'd guess with a pg_Exec(connect_id,"INSERT ...."). From reading the doc, > > I think that you can get the row just inserted by using the pg_fetch_row() > > function, passing it the result from the pg_Exec and asking for row 0. > > OK, I just tried pg_fetch_array() and the above didn't work. > > > I have not yet gotten anything running with PHP (lack of time to "play"), > > For someone who hasn't had time to play, you certainly know alot about php :) > > If you need any examples, I'd be willing to post them, time permitting. > I thought libpq was easy to use, this is very straightforward. I have been > 'playing' with it for a few days, and it is my development tool of choice > now. Of course, this is because of faster development time, don't expect > Yahoo! to replace their cgi with PHP just yet. > > > so I can't test this. If it doesn't work, I'd try using pg_GetLastOid() > > to get the OID of the inserted row. The use the pg_Exec and SELECT > > * WHERE OID=oid-value, followed by pg_fetch_row(). > > Thanks John, and the other person that replied to my email (I know it is a > bit of a stupid question, but in such an unpopulated list, I don't think > there's any reason to post privately). > This leads to another question. If someone adds another row during this, > what will happen? > > Thanks, > Alex >
On Tue, 13 Jun 2000 12:15:17 +1000, Alex <gliathit@ihug.com.au> wrote:[snip] > >> so I can't test this. If it doesn't work, I'd try using pg_GetLastOid() >> to get the OID of the inserted row. The use the pg_Exec and SELECT >> * WHERE OID=oid-value, followed by pg_fetch_row(). > >Thanks John, and the other person that replied to my email (I know it is a >bit of a stupid question, but in such an unpopulated list, I don't think >there's any reason to post privately). > This leads to another question. If someone adds another row during this, >what will happen? From what I can tell, the function pg_GetLastOid() does not do a database query. It appears that it simply fetches a value which was returned by PostgreSQL during the pg_Exec() call. Therefore, there should not be any problem with another, concurrent, execution of this PHP script. The pg_GetLastOid() is really more of pg_GetLastOid_of_this_execution, not "global" to the database (not GetLastOid_of_the_entire_database).
Alex <gliathit@ihug.com.au> writes: <pasted from earlier post> > Hi, > after running a script which performs an insert, a new tuple is > created, and a serial number is generated for it. I want to write the > new tuple data back to the screen, including the new serial number. > My question is, do I have to do a search for the tuple just inserted in > order to get the data back again? I am thinking there must be a faster, > more efficient way. > Thanks, > Alex I had the same problem but was using Java, not PHP (I guess that whatever I can do in JDBC, you can do in PHP ;-)). SELECT last_value FROM <sequence>; worked fine to retrieve the last-used serial number. I could have used SELECT i.* FROM <insert_table> i,<sequence> s WHERE i.serial=s.last_value; to retrieve the tuple, but I didn't need to do this. > This leads to another question. If someone adds another row during this, > what will happen? In JDBC I would turn off auto-commit mode, then commit after the SELECT. This should ensure that the sequence doesn't get incremented between INSERT and SELECT. > > Thanks, > Alex Hope this helps, Peter
Hi, > I had the same problem but was using Java, not PHP (I guess that > whatever I can do in JDBC, you can do in PHP ;-)). > > SELECT last_value FROM <sequence>; This brings me back to another unanswered question recently posted up, maybe it is impossible... I declared a new table with one of the types as serial (which is really just a sort of macro I believe, which automates the creation of a few things for your convenience), which initialises the last_value of the relation (the sequence itself), as 1. Therefore, upon INSERTing my first row, the serial number began at 1, next was 2, then 3, and so forth.My question is, is it possible to alter the sequence last_value column, as I need the serial number to begin from 1000? Thanks, Alex
Alex wrote in message <39487DD3.7F2BBAE1@ihug.com.au>... >Hi, > >> I had the same problem but was using Java, not PHP (I guess that >> whatever I can do in JDBC, you can do in PHP ;-)). >> >> SELECT last_value FROM <sequence>; > >This brings me back to another unanswered question recently posted up, maybe >it is impossible... >I declared a new table with one of the types as serial (which is really just a >sort of macro I believe, which automates the creation of a few things for your >convenience), which initialises the last_value of the relation (the sequence >itself), as 1. Therefore, upon INSERTing my first row, the serial number began >at 1, next was 2, then 3, and so forth. > My question is, is it possible to alter the sequence last_value column, as >I need the serial number to begin from 1000? >Thanks, >Alex > Nope, I tried doing an UPDATE name-of-sequence SET last_value=200 and I got an error about not being able to update the sequence. However, always being one to hit a brick until it cracks, I then tried: DROP name-of-sequence; CREATE name-of-sequence START 1000; This seemed to work! When I then INSERTed a new tuple, the attribute defined as SERIAL actually got the value of 1000. Curious, but nice. John McKown (note - not! Jack, but John)
> This brings me back to another unanswered question recently posted up,maybe > it is impossible... > I declared a new table with one of the types as serial (which is really just a > sort of macro I believe, which automates the creation of a few things for your > convenience), which initialises the last_value of the relation (the sequence > itself), as 1. Therefore, upon INSERTing my first row, the serial number began > at 1, next was 2, then 3, and so forth. > My question is, is it possible to alter the sequence last_value column, as > I need the serial number to begin from 1000? select setval(sequencename, value); should set the sequence as if value was the last value given out in the sequence.
do
select setval('seqname',#);
At 01:17 PM 6/15/00 -0500, John McKown wrote:
>
>Alex wrote in message <39487DD3.7F2BBAE1@ihug.com.au>...
>>Hi,
>>
>>> I had the same problem but was using Java, not PHP (I guess that
>>> whatever I can do in JDBC, you can do in PHP ;-)).
>>>
>>> SELECT last_value FROM <sequence>;
>>
>>This brings me back to another unanswered question recently posted up,
>maybe
>>it is impossible...
>>I declared a new table with one of the types as serial (which is really
>just a
>>sort of macro I believe, which automates the creation of a few things for
>your
>>convenience), which initialises the last_value of the relation (the
>sequence
>>itself), as 1. Therefore, upon INSERTing my first row, the serial number
>began
>>at 1, next was 2, then 3, and so forth.
>> My question is, is it possible to alter the sequence last_value column, as
>>I need the serial number to begin from 1000?
>>Thanks,
>>Alex
>>
>
>Nope, I tried doing an
>
>UPDATE name-of-sequence SET last_value=200
>
>and I got an error about not being able to update the sequence. However,
>always being one to hit a brick until it cracks, I then tried:
>
>DROP name-of-sequence;
>CREATE name-of-sequence START 1000;
>
>This seemed to work! When I then INSERTed a new tuple, the attribute defined
>as SERIAL actually got the value of 1000. Curious, but nice.
>
>John McKown (note - not! Jack, but John)
>
>