pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcurrval()
4+ messages / 3 participants
[nested] [flat]
* currval()
@ 2011-09-20 22:00 Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Marc Fromm @ 2011-09-20 22:00 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: currval()
@ 2011-09-20 22:52 Steve Crawford <scrawford@pinpointresearch.com>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
1 sibling, 1 reply; 4+ messages in thread
From: Steve Crawford @ 2011-09-20 22:52 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: currval()
@ 2011-09-20 23:54 Steve Crawford <scrawford@pinpointresearch.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 4+ messages in thread
From: Steve Crawford @ 2011-09-20 23:54 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: currval()
@ 2011-09-21 08:41 Julius Tuskenis <julius@nsoft.lt>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Julius Tuskenis @ 2011-09-21 08:41 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-09-21 08:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-20 22:00 currval() Marc Fromm <Marc.Fromm@wwu.edu>
2011-09-20 22:52 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-09-20 23:54 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-09-21 08:41 ` Julius Tuskenis <julius@nsoft.lt>
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