PostgreSQL:有效的变量分配示例? [英] PostgreSQL: a valid variable assignation sample?
问题描述
阅读这个问题后,我正在尝试转换一些SQL从MySQL到PostgreSQL。因此,我需要变量赋值:
After reading this question, I'm trying to convert some SQL from MySQL to PostgreSQL. Thus I need variable assignation:
INSERT INTO main_categorie (description) VALUES ('Verbe normal');
SET @PRONOMINAL := SELECT LAST_INSERT_ID();
INSERT INTO main_mot (txt,im,date_c,date_v_d,date_l)
VALUES ('je m''abaisse',1,NOW(),NOW(),NOW());
SET @verbe_149 = SELECT LAST_INSERT_ID();
INSERT INTO main_motcategorie (mot_id,categorie_id) VALUES (@verbe_149,@PRONOMINAL);
如何使用PostgreSQL? v9和v8 (几乎相同)。
注意:我不想使用在这里,我只想要原始sql,以便可以通过CLI界面将其注入。
How would you do this with PostgreSQL? No useful sample in the documentation of v9 and v8 (almost the same). NB: I dont want to use a stored procedure like here, I just want "raw sql" so I can inject it through CLI interface.
推荐答案
Postgres SQL中没有变量(只能在过程语言中使用变量)。
There are no variables in Postgres SQL (you can use variables only in procedural languages).
在<<中使用 RETURNING
code> WITH 查询:
Use RETURNING
in WITH
query:
WITH insert_cat AS (
INSERT INTO main_categorie (description)
VALUES ('Verbe normal')
RETURNING id
),
insert_mot AS (
INSERT INTO main_mot (txt,im,date_c,date_v_d,date_l)
VALUES ('je m''abaisse',1,NOW(),NOW(),NOW())
RETURNING id
)
INSERT INTO main_motcategorie (mot_id,categorie_id)
SELECT m.id, c.id
FROM insert_mot m, insert_cat c;
作为替代方案,您可以使用自定义配置参数按照这篇文章中所述的方式。
创建两个函数:
create or replace function set_var (name text, value text)
returns void language plpgsql as $$
begin
execute format('set mysql.%s to %s', name, value);
end $$;
create or replace function get_var (name text)
returns text language plpgsql as $$
declare
rslt text;
begin
execute format('select current_setting(''mysql.%s'')', name) into rslt;
return rslt;
end $$;
使用函数可以模拟变量,例如:
With the functions you can simulate variables, like in the example:
INSERT INTO main_categorie (description)
VALUES ('Verbe normal');
SELECT set_var('PRONOMINAL', (SELECT currval('main_categorie_id_seq')::text));
INSERT INTO main_mot (txt,im,date_c,date_v_d,date_l)
VALUES ('je m''abaisse',1,NOW(),NOW(),NOW());
SELECT set_var('verbe_149', (SELECT currval('main_mot_id_seq')::text));
INSERT INTO main_motcategorie (mot_id,categorie_id)
SELECT get_var('verbe_149')::int, get_var('PRONOMINAL')::int;
这当然不是良好代码的示例。
特别是强制转换的麻烦。
但是,转换可以半自动完成。
This is certainly not an example of good code. Particularly the necessity of casting is troublesome. However, the conversion can be done semi-automatically.
这篇关于PostgreSQL:有效的变量分配示例?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!