我有3个表:post, tag和tag_post
post保存实际的帖子,tags保存到目前为止已经创建的标签,tag_post保存它们之间的关系。当创建一个新帖子时,会发生4件事:
-
所有还不存在的标签被创建
-
帖子已创建
-
所有的tag_post关系被创建
-
新创建的post返回给函数调用者
这些是表
/*
POST TABLES
*/
CREATE TABLE tag(
name text PRIMARY KEY,
check(name!='')
);
CREATE TABLE post(
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
title text,
url text,
author text NOT NULL references profile(name)
);
CREATE TABLE tag_post(
tag text REFERENCES tag(name),
post uuid REFERENCES post(id),
UNIQUE (tag, post)
);
我是这样做的:
/*
CREATE A NEW POST
*/
create or replace function create_post(
author text,
title text,
url text,
tags text[]
)
RETURNS post
as $$
DECLARE
inserted record;
begin
INSERT INTO tag(name)
SELECT v
FROM unnest(tags) g(v)
ON CONFLICT DO NOTHING;
INSERT INTO post(author, title, url)
values(author,title,url)
RETURNING * INTO inserted;
INSERT INTO tag_post(tag,post)
SELECT g.v, inserted.id FROM
unnest(tags) g(v);
RETURN inserted;
END $$
language plpgsql;
现在我还想返回与插入的行一起传递给函数的标记数组。基本上是这样的(它不工作,只是为了演示):
SELECT inserted.*, tags as tags FROM inserted join tags INTO inserted;
但是我不知道如何将两个变量组合成一个变量。有办法做到这一点吗?
我的做法如下:
SELECT row_to_json(sel)
FROM
(
SELECT inserted.*,tags
) sel
INTO inserted2;
RETURN inserted2;
inserted2是一个json变量