在 PostgreSQL 中替换 MySQL 的变量? [英] Substitute for MySQL's variables in PostgreSQL?

问题描述

我们经常使用快速一次性 SQL 文件在现有数据库中插入或更新数据.SQL 通常由开发人员编写，在开发系统上进行测试，然后使用 psql -U dbuser dbname psql -U dbuser dbname <;file.sql.

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')
);

虽然这有效，但子查询中有很多重复的代码.将 companies.id 和 users.id 的相关值存储在临时变量中会更好(更有效且更不容易出错).在这个解释的示例中，性能差异可能很小，但在实践中我们确实有更复杂的查询和更新，并且经常有超过三个更新/插入的记录.

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.

为 MySQL 编写的相同示例如下所示:

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);

有什么方法可以在 PostgreSQL 中实现类似的功能吗?

Is there any way to achieve something similar in PostgreSQL?

我看过的:

• psql 的会话变量(带有 \set):不能用于存储查询结果
• plpgsql:只能在一个过程中使用(我们仍在运行 8.4)
• 临时表:我不知道如何在不创建丑陋和复杂语句的情况下使用它们

• 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

如果 Postgres 没有直接的等价物，您认为生成此类更新文件最不笨拙的方法是什么?

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?

推荐答案

考虑使用 CTE 或用于查询值的子查询一次并多次插入它们.
这样，您可以用标准 SQL 替换 MySQL 样式变量.

Consider using CTEs or subqueries to query values once and inserted 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)

假设 companies.name 和 users.login 实际上是唯一的.多次点击将使要插入的行成倍增加.
阅读手册中的INSERT 命令.

Assuming that companies.name and users.login are, in fact, unique. Multiple hits would multiply the rows to be inserted.
Read about the INSERT command in the manual.

这是我的临时表测试设置，以防有人想快速查看:

Here is my test setup with temporary tables in case anyone wants to have 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);

这篇关于在 PostgreSQL 中替换 MySQL 的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！