Обсуждение: currval()

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

On 09/20/2011 04:06 PM, Marc Fromm wrote:

My postgres version is 8.1. Last I heard RETURNING id started in 8.2.

I'd advise upgrading. 8.1 is no-longer supported (end-of-life was last November) and 8.2 has an end-of-life date in just a couple months. You'll get security and bug fixes, performance benefits and, of course, those cool new features you need. :)

(No security fixes is often the issue that gets management attention if necessary.)

Is not my semicolon before the select statement make it two sequential staements?

I don’t get an sql error when it is executed.

Well, it is technically legal (and why SQL injection can work). Looks like you are using PHP and PHP says that if you bundle statements together they will be executed as a transaction.

Perhaps it's personal preference, but combining statements like that is difficult to read and I suspect will make bugs hard to find. And although I can't find it documented, it appears that the return value is only from the final statement. I shy away from undocumented behaviour and I'd rather be able to check the result of each statement.

 

I did learn from another reply that my select statement was wrong. I needed to SELECT currval('jobs_id_seq') not the field name, which I was doing.

Yes, this is probably the real root of your problem. I did not notice that since I didn't have the table definition in front of me.

Cheers,
Steve

Hello, Mark

On 2011.09.21 01:00, Marc Fromm wrote:

The field name is ‘id’ and it is a primary key.

...

                $sql .= "SELECT currval('id');";

Marc

You should pass sequence name to currval (not field name like you do now). If you use pgAdmin sequence name is easily found looking at fields definition near the DEFAULT.

example:

ALTER TABLE b_preke ADD COLUMN prek_id integer;
ALTER TABLE b_preke ALTER COLUMN prek_id SET NOT NULL;
ALTER TABLE b_preke ALTER COLUMN prek_id SET DEFAULT nextval('b_preke_prek_id_seq'::regclass);

the field is prek_id and the sequence is b_preke_prek_id_seq

-- 
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050