Replace MySQL variables in PostgreSQL?

Question

We often use quick one-time SQL files to insert or update data in an existing database. SQL is usually written by the developer, tested in the development system, and then imported into the production database using psql -U dbuser dbname < file.sql .

An example (trivial) might look like this:

 INSERT INTO employees ( company_id, name, position, created_by, last_modified_by ) VALUES ( (SELECT id FROM companies WHERE name = 'Acme Fellowship'), 'Frodo Baggins', 'Ring bearer', (SELECT id FROM users WHERE login = 'admin'), (SELECT id FROM users WHERE login = 'admin') ), ( (SELECT id FROM companies WHERE name = 'Acme Fellowship'), 'Samwise Gamgee', 'Rope bearer', (SELECT id FROM users WHERE login = 'admin'), (SELECT id FROM users WHERE login = 'admin') ), ( (SELECT id FROM companies WHERE name = 'Acme Fellowship'), 'Peregrin Took', 'Ent rider', (SELECT id FROM users WHERE login = 'admin'), (SELECT id FROM users WHERE login = 'admin') ); 

While this works, there are a lot of duplicate code in the subqueries. It would be nice (more efficient and less error prone) to store the corresponding values ​​for companies.id and users.id in temporary variables. In this interpreted example, the performance difference is probably minimal, but in practice we have more complex queries and updates, and often there are more than three updated / inserted records.

The same example written for MySQL is as follows:

 SELECT @company_id := id FROM companies WHERE name = 'Acme Fellowship'; SELECT @admin_id := id FROM users WHERE login = 'admin'; INSERT INTO employees ( company_id, name, position, created_by, last_modified_by ) VALUES (@company_id, 'Frodo Baggins', 'Ring bearer', @admin_id, @admin_id), (@company_id, 'Samwise Gamgee', 'Rope bearer', @admin_id, @admin_id), (@company_id, 'Peregrin Took', 'Ent rider', @admin_id, @admin_id); 

Is there a way to achieve something similar in PostgreSQL?

What I looked at:

• psql session variables (with \set ): cannot be used to store query results
• plpgsql: can only be used in a procedure (we are still running 8.4)
• temporary tables: I don't see how to use them without creating ugly and confusing statements

If there is no direct equivalent for Postgres, what do you think would be the least awkward way to create this kind of update files?

Answer 1

Use CTE or subqueries to request values ​​once and insert them many times. This way you can replace MySQL style variables with standard SQL .

 INSERT INTO employees (company_id, name, position, created_by, last_modified_by) SELECT c.id , name, position, u.id , u.id FROM (SELECT id FROM companies WHERE name = 'Acme Fellowship') c ,(SELECT id FROM users WHERE login = 'admin') u ,(VALUES ('Frodo Baggins', 'Ring bearer') ,('Samwise Gamgee', 'Rope bearer') ,('Peregrin Took', 'Ent rider') ) v(name, position) 

Assuming companies.name and users.login are essentially unique. Multiple hits will multiply the rows to be inserted.
Read the INSERT manual in the manual .

---

Here is my test setup with temporary tables in case someone wants a quick look:

 CREATE TEMP TABLE companies (id int, name text); INSERT INTO companies VALUES (17, 'Acme Fellowship'); CREATE TEMP TABLE users (id int, login text); INSERT INTO users VALUES (9, 'admin'); CREATE TEMP TABLE employees ( company_id int ,name text ,position text ,created_by int ,last_modified_by int); 

Answer 2

Use VALUES () in SELECT, which should work:

 INSERT INTO employees ( company_id, name, position, created_by, last_modified_by ) SELECT (SELECT id FROM companies WHERE name = 'Acme Fellowship'), name, position, (SELECT id FROM users WHERE login = 'admin'), (SELECT id FROM users WHERE login = 'admin') FROM (VALUES -- all your new content here ('Frodo Baggins', 'Ring bearer'), ('Samwise Gamgee', 'Rope bearer'), ('Peregrin Took', 'Ent rider') ) content(name, position); -- use some aliases to make it readable 

Answer 3

This is an old question, but I found that using WITH statements made my life easier :)

 WITH c AS ( SELECT company_id, FROM companies WHERE name = 'Acme Fellowship' ), u AS ( SELECT * FROM users WHERE login = 'admin' ), n AS ( SELECT * FROM (VALUES -- all your new content here ('Frodo Baggins', 'Ring bearer'), ('Samwise Gamgee', 'Rope bearer'), ('Peregrin Took', 'Ent rider') ) content(name, position) ) INSERT INTO employees ( company_id, name, position, created_by, last_modified_by ) SELECT c.company_id, n.name, n.position, u.id, u.id FROM c, u, n