I had a really odd and unexpected behaviour in PostgreSQL. Debugging I found that the problem only occurred while including
CREATE TEMP TABLE ... inside a function, and by using an alternative (in this case, I resorted to an array) the error would be gone. Perhaps any one experienced a similar error or knows what this error relates to? I might need to create temporary tables in functions sooner or later.
I'm porting an old Delphi XE6 program from a legacy proprietary database to PostgreSQL, changing the TxxTable components (proprietary) to TFDTable ones (FireDAC) pointing to a copy of the database in PostgreSQL 9.5.
Things were working peachy until I created this plpgsql function that inserts and updates some data here and there. I ran the function via
PGConnection.ExecSQLScalar('SELECT do_stuff(:id)', FID);, where
PGConnection is my TFDConnection to PostgreSQL (same where I have many other tables open and working well),
do_stuff is the function in question, and
FID is an ID I send as a parameter. The function runs well, as it does when running it from PgAdmin, but the connection becomes unstable as any already open table afterwards trying to do anything (insert/edit/move) will raise the following error:
ERROR: cached plan must not change result type
STATEMENT: FETCH FORWARD 50 FROM "357STM"`
FireDAC normally opens tables and queries using cursors:
LOG: execute 358STM: DECLARE "357STM" CURSOR WITH HOLD FOR SELECT *
If I execute
do_stuff(:id) via an TFDQuery component, the function itself fails (FireDAC executes it as a cursor), raising the same error stated above.
After tearing my hear out debugging, I realised that the only way to avoid the error was avoid creating any temporary tables inside the function. Whereas creating the function as
CREATE TEMP TABLE tbl ON COMMIT DROP AS SELECT * FROM some_table WHERE .... or as
CREATE TEMP TABLE tbl (id int, value text); the error was raised. Without
CREATE TEMP TABLE, no errors raised.
Any clues will be highly appreciated!