pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Simple search question
10+ messages / 6 participants
[nested] [flat]

* Simple search question
@ 2000-06-12 23:42 Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  0 siblings, 1 reply; 10+ messages in thread

From: Alex @ 2000-06-12 23:42 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
@ 2000-06-13 01:09 ` John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 02:15   ` Re: Simple search question Alex <gliathit@ihug.com.au>
  0 siblings, 1 reply; 10+ messages in thread

From: newsuser@linux2.johnmckown.net @ 2000-06-13 01:09 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
@ 2000-06-13 02:15   ` Alex <gliathit@ihug.com.au>
  2000-06-13 02:30     ` Re: Re: Simple search question Michael Fork <mfork@toledolink.com>
  2000-06-13 10:59     ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 11:25     ` Re: Simple search question Peter Dixon <biz@ptdixon.globalnet.co.uk>
  0 siblings, 3 replies; 10+ messages in thread

From: Alex @ 2000-06-13 02:15 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 02:15   ` Re: Simple search question Alex <gliathit@ihug.com.au>
@ 2000-06-13 02:30     ` Michael Fork <mfork@toledolink.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Michael Fork @ 2000-06-13 02:30 UTC (permalink / raw)
  To: Alex <gliathit@ihug.com.au>; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 02:15   ` Re: Simple search question Alex <gliathit@ihug.com.au>
@ 2000-06-13 10:59     ` John McKown <newsuser@linux2.johnmckown.net>
  2 siblings, 0 replies; 10+ messages in thread

From: newsuser@linux2.johnmckown.net @ 2000-06-13 10:59 UTC (permalink / raw)
  To: pgsql-sql

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?



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 02:15   ` Re: Simple search question Alex <gliathit@ihug.com.au>
@ 2000-06-13 11:25     ` Peter Dixon <biz@ptdixon.globalnet.co.uk>
  2000-06-15 06:55       ` Re: Simple search question Alex <gliathit@ihug.com.au>
  2 siblings, 1 reply; 10+ messages in thread

From: Peter Dixon @ 2000-06-13 11:25 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 02:15   ` Re: Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 11:25     ` Re: Simple search question Peter Dixon <biz@ptdixon.globalnet.co.uk>
@ 2000-06-15 06:55       ` Alex <gliathit@ihug.com.au>
  2000-06-15 18:17         ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-15 19:09         ` Re: Re: Simple search question Stephan Szabo <sszabo@kick.com>
  2000-06-20 17:09         ` Re: Re: Simple search question mikeo <mikeo@spectrumtelecorp.com>
  0 siblings, 3 replies; 10+ messages in thread

From: Alex @ 2000-06-15 06:55 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 02:15   ` Re: Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 11:25     ` Re: Simple search question Peter Dixon <biz@ptdixon.globalnet.co.uk>
  2000-06-15 06:55       ` Re: Simple search question Alex <gliathit@ihug.com.au>
@ 2000-06-15 18:17         ` John McKown <newsuser@linux2.johnmckown.net>
  2 siblings, 0 replies; 10+ messages in thread

From: John McKown @ 2000-06-15 18:17 UTC (permalink / raw)
  To: pgsql-sql


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)





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 02:15   ` Re: Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 11:25     ` Re: Simple search question Peter Dixon <biz@ptdixon.globalnet.co.uk>
  2000-06-15 06:55       ` Re: Simple search question Alex <gliathit@ihug.com.au>
@ 2000-06-15 19:09         ` Stephan Szabo <sszabo@kick.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Stephan Szabo @ 2000-06-15 19:09 UTC (permalink / raw)
  To: Alex <gliathit@ihug.com.au>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Re: Simple search question
  2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 01:09 ` Re: Simple search question John McKown <newsuser@linux2.johnmckown.net>
  2000-06-13 02:15   ` Re: Simple search question Alex <gliathit@ihug.com.au>
  2000-06-13 11:25     ` Re: Simple search question Peter Dixon <biz@ptdixon.globalnet.co.uk>
  2000-06-15 06:55       ` Re: Simple search question Alex <gliathit@ihug.com.au>
@ 2000-06-20 17:09         ` mikeo <mikeo@spectrumtelecorp.com>
  2 siblings, 0 replies; 10+ messages in thread

From: mikeo @ 2000-06-20 17:09 UTC (permalink / raw)
  To: John McKown <newsuser@linux2.johnmckown.net>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2000-06-20 17:09 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-12 23:42 Simple search question Alex <gliathit@ihug.com.au>
2000-06-13 01:09 ` John McKown <newsuser@linux2.johnmckown.net>
2000-06-13 02:15   ` Alex <gliathit@ihug.com.au>
2000-06-13 02:30     ` Michael Fork <mfork@toledolink.com>
2000-06-13 10:59     ` John McKown <newsuser@linux2.johnmckown.net>
2000-06-13 11:25     ` Peter Dixon <biz@ptdixon.globalnet.co.uk>
2000-06-15 06:55       ` Alex <gliathit@ihug.com.au>
2000-06-15 18:17         ` John McKown <newsuser@linux2.johnmckown.net>
2000-06-15 19:09         ` Stephan Szabo <sszabo@kick.com>
2000-06-20 17:09         ` mikeo <mikeo@spectrumtelecorp.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox