Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like?
The following attempts were unsuccessful:

EXECUTE 'INSERT INTO my_table VALUES ' || NEW;
EXECUTE 'INSERT INTO my_table VALUES ' || NEW.*;
EXECUTE 'INSERT INTO my_table SELECT * FROM ' || NEW;
share|improve this question
add comment

migrated from gis.stackexchange.com Feb 7 at 14:32

This question came from our site for cartographers, geographers and GIS professionals.

2 Answers

up vote 3 down vote accepted

You must expand the record.

I've also added a column list to the insert and used the much safer EXECUTE ... USING form.

EXECUTE 'INSERT INTO my_table (col1, col2, col3) VALUES (?, ?, ?)' USING NEW.col1, NEW.col2, NEW.col3;

Please explain your use case in more detail if this is doesn't solve the issue.

share|improve this answer
add comment

Actually, you do not have to expand the record manually. As long as number, sequence and types of columns match between the two tables, you can use this much simpler form:

EXECUTE 'INSERT INTO my_table SELECT ($1).*'
USING NEW;

Since your table name seems to be stable, you do not even need dynamic SQL with EXECUTE. Just a plain INSERT:

INSERT INTO my_table SELECT (NEW).*

The parentheses are required for a composite type to make the syntax unambiguous.

Ask yourself what may change in your app in the future:

  • If the target table should stay in sync with the source table (or target and source are the same), then it's better not to list columns explicitly.

  • If you want to select only certain columns and do not want to change that with possible updates to the source or target table, it's better to supply a column list.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.