I have created trigger function that will try to insert data into remote database table like this:
db_res := dblink_exec('dbname=test user=test password=test host=localhost port=5432', 'INSERT INTO test (table, action) VALUES (''table'',''action'')');
When this trigger launches, it throws me this error:
syntax error (at or near: ",")
Why this is happening? How simple comma can cause this error?
table
is a reserved word in SQL, so using it as a column name will cause this syntax error. If the column is really called table
, you have to refer to it using the quoted identifier "table"
.
In order to avoid problems like this, it is highly commendable to not name objects with reserved keywords. You can find a list of those keywords in appendix C of the documentation.