Re: default to WITHOUT OIDS?

Neil Conway wrote:

>Folks,
>
>Is it a good idea for CREATE TABLE to default to WITHOUT OIDS, rather
>than WITH OIDS? Naturally, this would (a) be some time in the future
>(7.5, perhaps) and (b) only apply to user tables.
><snip>
>
>The main disadvantage I can see is just backward compatibility. In order
>to improve that, we could add a GUC var "use_oids_default" (or some
>other name), which would control whether a CREATE TABLE defaults to WITH
>or WITHOUT OIDS. In 7.4, the GUC var would default to false (so there
>would be no change in behavior), but in 7.5 we could switch it to true.
>
>Comments?
>
The problem with getting rid of OIDs as default is there is then no way
to get the primary key of a just inserted row with out OIDs (as far as I
know)

Oracle has the ability to bind variable which allows you to use
RETURNING INTO, Postgresql only has (eg in PHP) pg_getlastoid.

eg

assuming:

Pg:
CREATE TABLE testtable(test_id serial, text1 text);

Oracle:
CREATE TABLE testtable(test_id integer, text1 VARCHAR(255));
CREATE SEQUENCE test_id_seq INCREMENT BY 1;

Postgresql
$sql = "INSERT INTO testtable(text1) ";
$sql .= "VALUES(" . $some_str . ") ";
$result = pg_exec($conn, $sql);
$oid = pg_getlastoid($result);

$sql_pk = "SELECT test_id FROM testtable WHERE oid = '$oid'";
$result_array = pg_fetch_array($result, 0);
$pk = $result_array[0];

Oracle
$sql = "INSERT INTO testtable(test_id, text1) ";
$sql .= "VALUES(test_id_seq.nextval, :text1) RETURNING test_id INTO
:test_id";
$stmt = ociparse($conn,$sql);
ocibindbyname($stmt, :text1) &$some_str, -1);
ocibindbyname($stmt, ':test_id', &$test_id, 32);
ociexecute($stmt,OCI_DEFAULT);

No OIDs, no way to get a handle to a just inserted row.

>
>Cheers,
>
>Neil
>
>
Ashley Cambrell