Postgres - select, order and combine results

user_1077485 0
Viewed 34

In an app I am writing I am automatically generating a "callsign" for each user when they first register with the server. The callsign bears the format adjective adjective creature. The underlying Postgres tables from which I pick these values are as follows

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

When generating a new callsign I need to do the following

  • Pick two adjectives and a creature at random from their respective tables
  • Order the adjectives in ascending order of quality. In order to respect the rules of English grammar I need the right adjective order. Big Red Bull, for instance is right. Red Big Bull is not.
  • I need to check that the generated call sign does not already exist in the users table shown below
CREATE TABLE users ("user" character varying(16) NOT NULL,"callsign CHARACTER VARYING(32) NOT NULL);

The users table has much else - I have trimmed the details.

While I can work with basic SQL selects and do the rest in server-side script I suspect that there is a smart SQL only way of doing much, if not all, of the above in order to generate a unique, grammatically valid callsign. My rather limited knowledge of SQL is not quite up to that task. How can this be done?

postgresql
1 Answers

The quickest way to code this is to get the Cartesian product of adjectives X adjectives X creatures, remove the ones already used, and then select a random one from the list:

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
;

If you do not want to allow doubled adjective values, then change the inequality to > instead of >=.

This will not scale well if you get into hundreds of adjectives and creatures, but I cannot think of how to avoid a Cartesian product unless we bring iteration into the solution.

Working fiddle.

user_138083190
Related