StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSubstitute for MySQL's variables in PostgreSQL?
text
Body
copied!<p>We often use quick one-off SQL files to insert or update data in an existing database. The SQL is usually written by a developer, tested on the development system, and then imported in the production DB with <code>psql -U dbuser dbname < file.sql</code>.</p> <p>A (trivial) example might look like this:</p> <pre><code>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') ); </code></pre> <p>While this works, there's a lot of repetitive code in the subqueries. It would be nice (more efficient and less error prone) to store the relevant values for <code>companies.id</code> and <code>users.id</code> in temporary variables. In this construed example, the performance difference is likely minimal, but in practice we do have more complex queries and updates, and there are often more than three updated/inserted records.</p> <p>The same example written for MySQL looks like this:</p> <pre><code>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); </code></pre> <p>Is there any way to achieve something similar in PostgreSQL?</p> <p>What I've looked at:</p> <ul> <li>psql's session variables (with <code>\set</code>): cannot be used to store query results</li> <li>plpgsql: can only be used in a procedure (we're still running 8.4)</li> <li>temporary tables: I can't see how to use them without creating ugly and convoluted statements</li> </ul> <p>If there is no direct equivalent for Postgres, what do you think would be the least clumsy way to produce update files of this kind?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload