Postgresql: execute update on a temporary table in plpgsql is not working

Question

I'm trying to update a field in a temporary table I've created.

The code for the temporary table looks like this:

CREATE OR REPLACE FUNCTION insertTable ()
RETURNS VOID AS $$
BEGIN
execute 'create temporary table myTable (id INTEGER, value TEXT) on commit preserve rows';
execute 'insert into myTable values(1, NULL)';
end if;
end;
$$ LANGUAGE plpgsql;

Next I try to update the value filed with the following function:

CREATE OR REPLACE FUNCTION setValue (msg TEXT)
RETURNS VOID AS $$
BEGIN
EXECUTE format('UPDATE myTable SET value = value || $1 WHERE id = '|| quote_literal(1))USING msg;
END;
$$ LANGUAGE plpgsql;

However it does not work and the value field stays empty.

I tried the same code with an already existing table (not temporary) and the code worked as expected.

I searched the documentation for a difference between updating a temporary and a normal table but couldn't find any. I hope you can help me with this.

Thanks in advance for your help.

Edit: edited the name of the table

Answer 1

The issue is not related to temporary table. The problem is that the column you want to update is actually empty. You try to update this column by concatenating the value of the column with another text, but, because the value itself is null, the concatenated value is also null.

This query:

SELECT null||'some text';

returns null. Also this update statement:

UPDATE xmlBuffer SET value = value || 'some text';

will not update the rows where the actual content is null. You could fix this issue in several ways, depending on you needs. In example you could use the COALESCE statement in the second function, using a empty string as fallback value (besides, the quote_literal and formatstatements are not necessary):

CREATE OR REPLACE FUNCTION setValue (msg TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE xmlBuffer SET value = COALESCE(value,'''') || $1 WHERE id = '|| 1 
  USING msg;
END;
$$ LANGUAGE plpgsql;