So if INSERT is called outside the function, GET DIAGNOSTICS called inside the function cannot get the OID. Note that you cannot create a user-defined function (UDF) to return OID using GET DIAGNOSTICS for any INSERT statement executed outside the UDF as GET DIAGNOSTICS works only for last SQL command. When you call add_airport2 function, it also returns OID: Then when you call add_airport function, it returns OID:Īdditionally, you can use GET DIAGNOSTICS statement to retrieve the last OID:ĬREATE OR REPLACE FUNCTION add_airport2 (name VARCHAR (90 ) ) RETURNS INT AS $$īEGIN INSERT INTO airports VALUES (name ) Or into a variable in a PL/pgSQL function:ĬREATE OR REPLACE FUNCTION add_airport (name VARCHAR (90 ) ) RETURNS INT AS $$īEGIN INSERT INTO airports VALUES (name ) RETURNING oid INTO id Return OID as a single row result set INSERT INTO airports VALUES ( 'Dulles' ) RETURNING oid You can use RETURNING option of INSERT statement to return as a single row result set:
There are several reliable ways how to get the latest inserted OID value in the current session: INSERT INTO … SELECT loses OIDs (new OIDs are generated) Pg_dump preserves OIDs if -o option is used GET DIAGNOSTICS varname = RESULT_OID a PL/pgSQL statement INSERT … RETURNING oid returns OID immediately after INSERT statement * does not include oid to the column list, but you can use SELECT *, oid FROM …Īcross all tables until the max value is reached
Starts with 1 after reaching the max value, so duplicates can appear (4) The Definition of Tuples Each line in a relation. Can be changed by default_with_oids server optionģ2-bit unsigned integer - 2 32-1 (4 billion) For example, a relation of student ID and the student name should include the sid (string, Not NULL, UNIQUE) and the name (string, Not NULL).