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: But dropping serialness from an existing column or adding it to an existing column is not straightforward. You can specify serial as a column type when creating a table or when adding a column.
Since serial is not a real type, it can only be used in certain circumstances. 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 ) ALTER TABLE testnew ALTER COLUMN id RESTART WITH 1000 Schema management. 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:
#Column of relation does not exist postgres pgadmin 4 how to
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 Can be changed by default_with_oids server optionģ2-bit unsigned integer - 2 32-1 (4 billion)