Postgres-选择、排序和组合结果

Question

在我正在编写的应用程序中，我会自动生成一个"；呼号"；当每个用户第一次向服务器注册时。呼号的格式为adjective adjective creature。我从底层Postgres表中选择这些值如下

CREATE TABLE adjectives ("adj" character varying(16) NOT NULL,"quality" smallint NOT NULL,
CONSTRAINT "adjectives_adj_key" UNIQUE ("adj")) WITH (oids = false);
INSERT INTO adjectives (adj,quality) VALUES ('bold',1),('witty',1),('red',5),('old',3);
CREATE TABLE creatures ("creature" character varying(16) NOT NULL,"quality" smallint NOT NULL,
CONSTRAINT "creatures_creature_key" UNIQUE ("creature")) WITH (oids = false);
INSERT INTO creatures (creature,quality) VALUES ('tiger',1),('dog',1),('sparrow',5), 
('dolphin',3);

生成新呼号时，我需要执行以下

• 从各自的表格中随机选择两个形容词和一个生物
• 按质量升序排列形容词。为了遵守英语语法规则，我需要正确的形容词顺序。例如Big Red Bull是对的。Red Big Bull不是
• 我需要检查生成的呼号是否已存在于下面显示的用户表中

CREATE TABLE users ("user" character varying(16) NOT NULL,"callsign CHARACTER VARYING(32) NOT NULL);

users表还有很多其他内容——我已经修剪了细节。

虽然我可以使用基本的SQL选择并在服务器端脚本中完成其余部分，但我怀疑有一种智能SQL唯一的方法可以完成以上大部分(如果不是全部的话(，以便生成一个唯一的、语法有效的调用号。我对SQL相当有限的知识无法胜任这项任务。如何做到这一点？

Answer 1

对此进行编码的最快方法是获得adjectivesXadjectivesXcreatures的笛卡尔乘积，删除已使用的乘积，然后从列表中随机选择一个：

select concat(
a1.adj, ' ',
a2.adj, ' ',
c.creature
)
from adjectives a1
join adjectives a2
on a2.quality >= a1.quality
cross join creatures c
where not exists (select 1 
from users
where callsign = concat(
a1.adj, ' ',
a2.adj, ' ',
c.creature))
order by random()
limit 1
;

如果不希望adjective值加倍，请将不等式更改为>，而不是>=。

如果你进入数百个adjectives和creatures，这将不会很好地扩展，但我想不出如何避免笛卡尔乘积，除非我们在解决方案中引入迭代。

工作小提琴。