Substitute for MySQL's variables in PostgreSQL?

Question

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 psql -U dbuser dbname < file.sql.

A (trivial) example 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'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 companies.id and users.id 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.

The same example written for MySQL looks like this:

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 any way to achieve something similar in PostgreSQL?

What I've looked at:

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

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?

Answer 1

Use VALUES() in a SELECT, that 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