pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple search question
10+ messages / 6 participants
[nested] [flat]
* Simple search question
@ 2000-06-12 23:42 Alex <gliathit@ihug.com.au>
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-13 01:09 John McKown <newsuser@linux2.johnmckown.net>
parent: 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-13 02:15 Alex <gliathit@ihug.com.au>
parent: John McKown <newsuser@linux2.johnmckown.net>
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-13 02:30 Michael Fork <mfork@toledolink.com>
parent: Alex <gliathit@ihug.com.au>
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-13 10:59 John McKown <newsuser@linux2.johnmckown.net>
parent: Alex <gliathit@ihug.com.au>
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-13 11:25 Peter Dixon <biz@ptdixon.globalnet.co.uk>
parent: 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-15 06:55 Alex <gliathit@ihug.com.au>
parent: Peter Dixon <biz@ptdixon.globalnet.co.uk>
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-15 18:17 John McKown <newsuser@linux2.johnmckown.net>
parent: Alex <gliathit@ihug.com.au>
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-15 19:09 Stephan Szabo <sszabo@kick.com>
parent: Alex <gliathit@ihug.com.au>
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-20 17:09 mikeo <mikeo@spectrumtelecorp.com>
parent: Alex <gliathit@ihug.com.au>
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