CODEWITHSUNDEEP
postgresqlsql-insert
birgersp
birgersp

Reputation: 4916

How do I add to a table and then use the id for a different query?

I have three tables, which I create with these queries:

create table parents(
    rowid serial primary key,
    display_name varchar,
    unique(display_name)
);

create table clients(
    rowid serial primary key,
    parent int,
    display_name varchar,
    foreign key (parent) references parents(rowid),
    unique(display_name, parent)
);

create table datapoints(
    rowid serial primary key,
    client int,
    val float8,
    foreign key (client) references clients(rowid)
);

I am getting data which contains parent name, client name and some value. Each time I get this data, I want to add rows to the datapoints table. I also want to add rows to the clients and parents table, but only if the data I received has unrecognized names.

For example, I might get this data:

"parent1-client1-123.0"

What I would like to achieve is to process this data (in a query), this way:

Insert a row to the "parents" table, if needed.

Insert a row to the "clients" table with the appropriate parent id (obtained in the previous step?), if needed.

Insert a row to the "datapoints" table with the appropriate client id (obtained in the previous step?).

How can I manage this with queries? The programmer in me wants to write:

But obviously this is not how sql works, right? And what if, instead of these three "nested" tables I have 5, or 10?(!)

Please help

Upvotes: 0

Views: 682

Answers (1)

Belayer
Belayer

Reputation: 14886

Postgres can return the generated key on an insert. Unfortunately, when it encounters an existing unique key and an on constraint clause it does not return the existing key. (A guess, but you have probably discovered this.) However the programmer in you is not far off. Instead of retrieve and save the id you retrieve retrieve and use directly via a select. If needed for subsequent operations you just 'repeat' the retrieval. So with 3 levels you retrieve the first level value (parent rowid) for the 2nd level (client) and 3rd level (datapoints). The best (imho) just create a procedure which handles the 3 levels. Further, break down the input into is components before calling the procedure. The resulting procedure becomes:

create or replace procedure establisth_parent_client_datapoint(
                            parent_name_in  varchar
                          , client_name_in  varchar
                          , val_in          float8
                          ) 
  language sql 
as $$
    insert into parents (display_name)
         values (parent_name_in) 
         on conflict (display_name)
            do nothing;
                        
    insert into clients(display_name, parent) 
         select client_name_in 
              , p.rowid 
           from parents p 
          where p.display_name = parent_name_in 
        on conflict (display_name, parent)
           do nothing; 
          
    insert into datapoints(val, client) 
         select val_in, c.rowid 
           from parents p            
           join clients c 
             on ( c.parent = p.rowid) 
          where c.display_name = client_name_in
            and p.display_name = parent_name_in; 
$$;

since I do not know your development language I will supply examples in a Postgres DO block. See demo here.
As far as having 5-10 nested tables, you just need to repeat the basic process for each. Of course with 10 nested tables, I would probably question the design.

Upvotes: 1

Related Questions