Thread

1. Simple search question

   Alex <gliathit@ihug.com.au> — 2000-06-12T23:42:01Z

   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
   
   
   

2. Re: Simple search question

   John McKown <newsuser@linux2.johnmckown.net> — 2000-06-13T01:09:11Z

   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
   
   

3. Re: Simple search question

   Alex <gliathit@ihug.com.au> — 2000-06-13T02:15:17Z

   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
   
   
   

4. Re: Re: Simple search question

   Michael Fork <mfork@toledolink.com> — 2000-06-13T02:30:33Z

   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
   > 
   
   
   

5. Re: Simple search question

   John McKown <newsuser@linux2.johnmckown.net> — 2000-06-13T10:59:13Z

   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).
   
   

6. Re: Simple search question

   Peter Dixon <biz@ptdixon.globalnet.co.uk> — 2000-06-13T11:25:09Z

   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
   
   

7. Re: Simple search question

   Alex <gliathit@ihug.com.au> — 2000-06-15T06:55:15Z

   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
   
   
   

8. Re: Simple search question

   John McKown <newsuser@linux2.johnmckown.net> — 2000-06-15T18:17:50Z

   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)
   
   
   
   

9. Re: Re: Simple search question

   Stephan Szabo <sszabo@kick.com> — 2000-06-15T19:09:39Z

   > 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.
   
   
   
   

10. Re: Re: Simple search question

    mikeo <mikeo@spectrumtelecorp.com> — 2000-06-20T17:09:56Z

    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)
    >
    >