The calculated identifier from the string as a database key

Question

I have an XML interface with entries that I need to parse and then paste into a database. Records have all the necessary information, but I want to normalize the database and therefore try to generate objects from the basic information.

This is an example of a base record:

<entry url="http://example.com" author="Ex1" title="Title" category="Category1"/>

And I want essentially the following basic objects (pseudocode):

class entry:
    entryID; (PK)
    url;
    title;
    authorID; (FK)
    categoryID; (FK)

class author:
    authorID; (PK)
    name;

class category:
    categoryID; (PK)
    name;

This, of course, creates a problem when I need to insert a category and an author before inserting the actual record so as not to violate the restriction of referential integrity, but I do not want to write authors and categories to the database just to immediately restore new keys and write them back to recording, as this will significantly reduce performance.

?

Answer 1

WITH , , :

WITH author AS (
    INSERT INTO d_author (name)
    values ($1) RETURNING id)
insert to maintable (id)
    select id from author

(, , . https://www.postgresql.org/docs/current/static/queries-with.html WITH).