Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 49A52B5DC3D
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 20 Sep 2011 19:52:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 14927-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 20 Sep 2011 22:52:50 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from polaris.pinpointresearch.com (cerberus.pinpointresearch.com
	[66.7.238.130])
	by mail.postgresql.org (Postfix) with ESMTP id 752CAB5DBC4
	for <pgsql-admin@postgresql.org>; Tue, 20 Sep 2011 19:52:49 -0300 (ADT)
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP id 64C98E00EC84
	for <pgsql-admin@postgresql.org>; Tue, 20 Sep 2011 15:52:48 -0700 (PDT)
Message-ID: <4E791940.1040903@pinpointresearch.com>
Date: Tue, 20 Sep 2011 15:52:48 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.21) Gecko/20110831 Lightning/1.0b2 Thunderbird/3.1.13
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: currval()
References: 
 <93361C67E4EE844A80935863A4FF4B6C051150A2@Exch2010MB-2.univ.dir.wwu.edu>
In-Reply-To: 
 <93361C67E4EE844A80935863A4FF4B6C051150A2@Exch2010MB-2.univ.dir.wwu.edu>
Content-Type: multipart/alternative;
	boundary="------------020201070909080603050109"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.403 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RP_MATCHES_RCVD=-0.504
X-Spam-Level: 
X-Archive-Number: 201109/142
X-Sequence-Number: 38379

This is a multi-part message in MIME format.
--------------020201070909080603050109
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------020201070909080603050109
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 09/20/2011 03:00 PM, Marc Fromm wrote:
    <blockquote
cite="mid:93361C67E4EE844A80935863A4FF4B6C051150A2@Exch2010MB-2.univ.dir.wwu.edu"
      type="cite">
      <meta http-equiv="Content-Type" content="text/html;
        charset=ISO-8859-1">
      <meta name="Generator" content="Microsoft Word 14 (filtered
        medium)">
      <style><!--
/* Font Definitions */
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri","sans-serif";}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
      <div class="WordSection1">
        <p class="MsoNormal">I am trying to get the id of the current
          inserted record. The field name is &#8216;id&#8217; and it is a primary
          key. I am obviously missing the correct syntax.<o:p></o:p></p>
        <p class="MsoNormal">I cannot use RETURNING id.<o:p></o:p></p>
        <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
        <p class="MsoNormal">$sql = "INSERT INTO jobs (job_title,
          num_positions, pay_min, pay_max,
          <o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          startdate, enddate, job_desc, job_benefits,
          min_qualifications, employer_contact,
          <o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          employer_phone, employer_email, stu_duties_id, stu_duties,
          grade_level,
          <o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          employer_school, send_to, reimbursement_amt, postdate)
          <o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          VALUES ('$job_title', '$num_positions', '$PAY_MIN',
          '$PAY_MAX',
          <o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          '$startdate', '$enddate', '$job_desc', '$job_benefits',
          '$min_qualifications', '$employer_contact',<o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          '$employer_phone', '$employer_email', '$stu_duties_id',
          '$DUTIES_DESC[$stu_duties_id]', '$grade_level',<o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          '$employer_school', '$send_to', '$reimbursement_amt',
          '$postdate');";<o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $sql .= "SELECT
          currval('id');";<o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $result = pg_query($conn,
          $sql);<o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $r =
          pg_fetch_object($result);<o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $jobs_id = $r-&gt;id;<o:p></o:p></p>
        <p class="MsoNormal">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; echo "jobs id: " . $jobs_id
          . "&lt;br /&gt;"; //echo is nothing<o:p></o:p></p>
        <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
        <p class="MsoNormal">Any insight would be appreciated.<o:p></o:p></p>
        <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
        <p class="MsoNormal">$sql echoed is this:<o:p></o:p></p>
        <p class="MsoNormal">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',
          '<a class="moz-txt-link-abbreviated" href="mailto:emailme@mail.com">emailme@mail.com</a>', '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);<o:p></o:p></p>
        <p class="MsoNormal"><o:p>&nbsp;</o:p></p>
        <p class="MsoNormal"><span style="font-size: 10pt; font-family:
            &quot;Courier New&quot;;">Marc</span><o:p></o:p></p>
      </div>
    </blockquote>
    <br>
    You are combining two statements into one - this is not correct.<br>
    <br>
    Not sure why you can't use returning - it works for me (where bar is
    some text and fooid is serial):<br>
    insert into footest (bar) values ('abcde') returning fooid;<br>
    <br>
    But if you really can't, then just execute the two statements
    sequentially:<br>
    insert into.....;<br>
    select currval('id');<br>
    <br>
    Cheers,<br>
    Steve<br>
  </body>
</html>

--------------020201070909080603050109--