[a_m0d]

This script works in SQL Server - I would think you could do the same in Postgres, but I'm not as familiar as I'd like to be with Postgres.

Note that it does require 2 separate steps rather than 1 as you appear to desire, so may not work for you:

    IF OBJECT_ID('dbo.dogs', 'U') IS NOT NULL
        DROP TABLE dbo.dogs
    GO
    
    IF OBJECT_ID('dbo.doghouses', 'U') IS NOT NULL
        DROP TABLE dbo.doghouses
    GO
    
    CREATE TABLE dogs (
        id INTEGER IDENTITY (1, 1) PRIMARY KEY
       ,name VARCHAR(MAX) NOT NULL
    );
    
    -- Now we want to give each dog a doghouse:
    CREATE TABLE doghouses (
        id INTEGER IDENTITY (1, 1) PRIMARY KEY
       ,name VARCHAR(MAX) NOT NULL
    );
    GO
    
    ALTER TABLE dogs ADD doghouse_id INTEGER REFERENCES doghouses (id);
    GO
    
    INSERT INTO dogs (name)
    VALUES
        ('Sparky'),
        ('Spot')
    ;
    
    DECLARE @DogsAndHouses TABLE (
        doghouse_id INT
       ,name VARCHAR(MAX)
    );
    
    INSERT INTO dbo.doghouses (name)
    OUTPUT INSERTED.id, INSERTED.name INTO @DogsAndHouses
    SELECT
        d.name
    FROM
        dbo.dogs d;
    
    UPDATE d
    SET
        doghouse_id = dah.doghouse_id
    FROM
        dogs d
        JOIN @DogsAndHouses dah ON d.name = dah.name;
    GO

[CWuestefeld]

Yup, the OUTPUT keyword is the key to making this work. Before this was added to TSQL, the only way I knew of to make this work was by using a cursor and handling each record independently.