Replace double quotes with single quotes in Postgres (plpgsql)

Question

I am building a trigger function in plpgsql for my partitioned table and I've got all of my logic working, but am having trouble inserting the actual record into my table.

I have to reference my specific table by a variable reference, so that (as far as I understand) forces me to use an EXECUTE command, as so:

EXECUTE 'INSERT INTO ' || tablename || ' VALUES ' || NEW.*;

However, this does not handle unpacking the record stored in NEW in a way that Postgres' INSERT function can understand. It converts the record into a string while preserving all of the double quotes within. I.e. the above turns into the following upon execution:

INSERT INTO cfs_hgt_05152016_05202016
VALUES ("2016-05-16 00:00:00","2016-05-12 06:00:00",HGT,"1000 mb",9,-71,-38.5371)

The problem with this is Postgres thinks these values are now columns due to the double quotes.

COPY cfs_hgt_master, line 1: ""2016-05-16 00:00:00","2016-05-12 06:00:00","HGT","1000 mb",9,-71,-38.5371"
ERROR:  column "2016-05-16 00:00:00" does not exist

I tried to remedy this by the following:

record_text := regexp_replace(NEW.*, '"', '\'', 'gi');
EXECUTE 'INSERT INTO ' || tablename || ' VALUES ' || record_text;

But escaping the single quote like that produces an error:

psql:cfs_hgt_trigger_function.sql:36: ERROR:  unterminated quoted string at or near "' || record_text;
LINE 30: ...  EXECUTE 'INSERT INTO ' || tablename || ' VALUES ' || recor...
                                                              ^

Could someone help me figure out how to either properly escape that single quote, or suggest an alternative means of accomplishing my task?

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2016-05-18T01:48:32

Don't convert values to their text representation at all. Pass values with the USING clause of EXECUTE.

And escape table names properly. You can use format() for this:

EXECUTE format('INSERT INTO %I SELECT $1.*', tablename)
USING NEW;

Details:

Replace double quotes with single quotes in Postgres (plpgsql)

1 Answers