From: Steve Crawford <scrawford@pinpointresearch.com>
To: pgsql-admin@postgresql.org
Subject: Re: currval()
Date: Tue, 20 Sep 2011 15:52:48 -0700
Message-ID: <4E791940.1040903@pinpointresearch.com> (raw)
In-Reply-To: <93361C67E4EE844A80935863A4FF4B6C051150A2@Exch2010MB-2.univ.dir.wwu.edu>
References: <93361C67E4EE844A80935863A4FF4B6C051150A2@Exch2010MB-2.univ.dir.wwu.edu>
On 09/20/2011 03:00 PM, Marc Fromm wrote:
>
> I am trying to get the id of the current inserted record. The field
> name is 'id' and it is a primary key. I am obviously missing the
> correct syntax.
>
> I cannot use RETURNING id.
>
> $sql = "INSERT INTO jobs (job_title, num_positions, pay_min, pay_max,
>
>
> startdate, enddate, job_desc, job_benefits, min_qualifications,
> employer_contact,
>
>
> employer_phone, employer_email, stu_duties_id, stu_duties, grade_level,
>
>
> employer_school, send_to, reimbursement_amt, postdate)
>
> VALUES
> ('$job_title', '$num_positions', '$PAY_MIN', '$PAY_MAX',
>
>
> '$startdate', '$enddate', '$job_desc', '$job_benefits',
> '$min_qualifications', '$employer_contact',
>
>
> '$employer_phone', '$employer_email', '$stu_duties_id',
> '$DUTIES_DESC[$stu_duties_id]', '$grade_level',
>
>
> '$employer_school', '$send_to', '$reimbursement_amt', '$postdate');";
>
> $sql .= "SELECT currval('id');";
>
> $result = pg_query($conn, $sql);
>
> $r = pg_fetch_object($result);
>
> $jobs_id = $r->id;
>
> echo "jobs id: " . $jobs_id . "<br />"; //echo is nothing
>
> Any insight would be appreciated.
>
> $sql echoed is this:
>
> INSERT INTO jobs (job_title, num_positions, pay_min, pay_max,
> startdate, enddate, job_desc, job_benefits, min_qualifications,
> employer_contact, employer_phone, employer_email, stu_duties_id,
> stu_duties, grade_level, employer_school, send_to, reimbursement_amt,
> postdate) VALUES ('Paraeducator', '1', '9.50', '9.50', '9/16/2011',
> '6/10/2012', 'fdfd', 'fdfd', 'fdfd', 'firstn lastn', '3351',
> 'emailme@mail.com', '2', 'This position mostly tutors, instructs, or
> directly assists students. Less than half of the position tasks are
> clerical or support duties.', '10', '28', 'all', '70%',
> '09-20-2011');SELECT currval(id);
>
> Marc
>
You are combining two statements into one - this is not correct.
Not sure why you can't use returning - it works for me (where bar is
some text and fooid is serial):
insert into footest (bar) values ('abcde') returning fooid;
But if you really can't, then just execute the two statements sequentially:
insert into.....;
select currval('id');
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com
Subject: Re: currval()
In-Reply-To: <4E791940.1040903@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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