Hi All,
We recently upgraded to PostgreSQL 11. We have discovered an undocumented (or well-hidden) new behavior in pl/pgSQL.
SELECT * INTO record variable does not include the OID column any more. All other system columns are still stored in the record variable. We tested this using a table WITH(OIDS).
PostgreSQL 9.5.18:
postgres=# create table test(id oid) with (oids);
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
PostgreSQL 9.6.14:
postgres=# create table test(id oid) with (oids);
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
PostgreSQL 10.9:
postgres=# create table test(id oid) with (oids);
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
DO
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
PostgreSQL 11.4:
postgres=# create table test(id oid) with (oids);
CREATE TABLE
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select * into _x from test; _o := _x.oid; end; $$;
ERROR: record "_x" has no field "oid"
CONTEXT: SQL statement "SELECT _x.oid"
PL/pgSQL function inline_code_block line 1 at assignment
postgres=# do language plpgsql $$ declare _x record; _o oid; begin select oid,* into _x from test; _o := _x.oid; end; $$;
DO
The PG11 documentation for pl/pgsql states that variable assignment to a record type executes a select statement, which PG11 seems to be doing as intended.
Was including the OID column in assignment a feature in previous versions?
Kind Regards,
Paolo Saul
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL
VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.