Neil Conway wrote:
>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.
>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.
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
Oracle has the ability to bind variable which allows you to use
RETURNING INTO, Postgresql only has (eg in PHP) pg_getlastoid.
CREATE TABLE testtable(test_id serial, text1 text);
CREATE TABLE testtable(test_id integer, text1 VARCHAR(255));
CREATE SEQUENCE test_id_seq INCREMENT BY 1;
$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;
$sql = "INSERT INTO testtable(test_id, text1) ";
$sql .= "VALUES(test_id_seq.nextval, :text1) RETURNING test_id INTO
$stmt = ociparse($conn,$sql);
ocibindbyname($stmt, :text1) &$some_str, -1);
ocibindbyname($stmt, ':test_id', &$test_id, 32);
No OIDs, no way to get a handle to a just inserted row.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster