Re: [SQL] Retrieving the new "nextval" for primary keys....
On Wed, 28 Aug 2002 18:36:10 +0200 (CEST) friedrich nietzsche <[EMAIL PROTECTED]> wrote: > One solution seems to locking table(s), > but I prefer to leave it as last chance... > using table locks, and the trick of writing and > suddenly reading back from DB it probably works, > but it doesn't seems so sexy... :) > ciao > danilo > Why would you have to lock the table? currval() is connection safe. I would either do the insert and then do a currval() OR do a nextval() and do the insert. Either one would work. I always just do the insert and then call currval() to get the current serial number for the connection. GB -- GB Clark II | Roaming FreeBSD Admin [EMAIL PROTECTED] | General Geek CTHULU for President - Why choose the lesser of two evils? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Retrieving the new nextval...
On Wed, 28 Aug 2002 18:32:45 +0200 (CEST)
friedrich nietzsche <[EMAIL PROTECTED]> wrote:
> Hi all,
> I'm in trouble with the same problem, but in PHP..
> With your solution, I cannot be totally sure that last
> inserted raw was mine...
> Because I'm on a web page, it could be that, as soon
> as I've inserted my record, another one do an
> insertion, so I would get the wrong ID...
> does transactions resolve this, in Psql???
> I thought to solve it with a similiar solution,
> working in transactions inserting a raw and
> immedialtly after read from DB last raw, but who
> assure me that all will go right??
> If I was on a server app., I (and you, if it is your
> case) would insert a timestamp, and then I'd select
> from table where timestamp = mysavedtime;
> But in my case there could be two or more equals
> timestamp, cause there's not only one application
> working with DB...
> I'm still reading, searching, trying...
> ciao
> danilo
If your on the same connection, then currval()/nextval() will do the
correct thing. i.e. if this is the same php page, then it should be the same
connection. If not, I would use php's session support to pass the sequence number
onto the next page.
currval()/nextval() are connection safe. As far as I know the php connection system
works correctly
(just don't try to use a DBM file at the same time as a pgsql connection... PHP does
NOT like that!)
GB
>
>
> --- Kevin Brannen <[EMAIL PROTECTED]> ha
> scritto: > Greg Patnude wrote:
> > > I am using postgreSQL with Perl::CGI and
> > Perl::DBI::Pg... I would like to be
> > > able to insert a row from my Perl script
> > [$SQL->exec();] and have postgreSQL
> > > return the id of the newly inserted record
> > (new.id) directly to the Perl
> > > script for further processing... Anyone with a
> > solution / idea ???
> > >
> > > Nearly EVERY table I create in postgreSQL (7.2)
> > has the following minimum
> > > structure:
> > >
> > > create table "tblName" (
> > >
> > > id int4 primary key nextval
> > ("tblName_id_seq"),
> > >
> > > ..field...
> > > )
> >
> > You can either do it in 2 statements, something
> > like:
> >
> > $dbh->do("insert into tblName ...");
> > my ($id) = $dbh->selectrow_array("select
> > currval('tblName_id_seq')");
> >
> > Or you could create a function which takes the
> > insert statement, and
> > ends with doing a select on the currval (as above)
> > and returning that.
> > As I do the 2 statement approach above, I haven't
> > done a function, but
> > it doesn't look like it would be that hard to do.
> >
> > HTH,
> > Kevin
> R
>
> __
> Yahoo! Musica: notizie, recensioni, classifiche, speciali multimediali
> http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
GB Clark II | Roaming FreeBSD Admin
[EMAIL PROTECTED] | General Geek
CTHULU for President - Why choose the lesser of two evils?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Constraint problems
Hello,
I'm trying to create a constraint that will check to see if the inserted data is found
in another column in the table.
It could be that I'm going about this the wrong way, any clues for me?
Here is the present setup data for the table.
--SNIP--
--
--
--
DROP SEQUENCE sysusers_user_id_seq;
CREATE SEQUENCE sysusers_user_id_seq START 1000;
DROP TABLE sysusers;
CREATE TABLE sysusers (
user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
UNIQUE NOT NULL PRIMARY KEY,
usernametext NOT NULL UNIQUE,
passwordtext NOT NULL,
createbytext NOT NULL DEFAULT 'syscreate',
status char(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
comment1text,
comment2text,
tstamp timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
INSERT INTO sysusers (username, password, createby) VALUES ('syscreate', 'testing',
'syscreate');
ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));
INSERT INTO sysusers (username, password) VALUES ('gclarkii', 'testing');
--SNIP--
It is the constraint on createby that I'm trying to get to work.
Thanks for any and all help.
GB
--
GB Clark II | Roaming FreeBSD Admin
[EMAIL PROTECTED] | General Geek
CTHULU for President - Why choose the lesser of two evils?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Constraint problems
On Fri, 27 Sep 2002 11:29:34 -0700 (PDT)
Stephan Szabo <[EMAIL PROTECTED]> wrote:
> On Fri, 27 Sep 2002, GB Clark wrote:
>
> > CREATE TABLE sysusers (
> > user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
> > UNIQUE NOT NULL PRIMARY KEY,
> > usernametext NOT NULL UNIQUE,
> > passwordtext NOT NULL,
> >
> > createbytext NOT NULL DEFAULT 'syscreate',
> >
> > status char(1) DEFAULT 'A' CHECK (status in ('A', 'I')),
> > comment1text,
> > comment2text,
> > tstamp timestamp DEFAULT CURRENT_TIMESTAMP
> > );
> >
> > CREATE UNIQUE INDEX username_unique_idx ON sysusers (username);
> >
> > INSERT INTO sysusers (username, password, createby) VALUES ('syscreate',
>'testing', 'syscreate');
> >
> > ALTER TABLE sysusers ADD CONSTRAINT createby_test CHECK (createby in (username));
>
> This is only going to check the value of username in the current row.
>
> Why not use a foreign key from createby to username?
>
Thanks! The FK was just what I was looking for. I had not relized that I could
do a current table reference in a FK declaration.
Thanks,
GB
--
GB Clark II | Roaming FreeBSD Admin
[EMAIL PROTECTED] | General Geek
CTHULU for President - Why choose the lesser of two evils?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
