Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37542=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1VfEr0-0008Px-Sg
 for pgsql-sql@arkaria.postgresql.org; Sat, 09 Nov 2013 20:09:59 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37542=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1VfEr0-0004zz-8n
 for pgsql-sql@arkaria.postgresql.org; Sat, 09 Nov 2013 20:09:58 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <css.liquid@gmail.com>) id 1VfEqz-0004zs-17
 for pgsql-sql@postgresql.org; Sat, 09 Nov 2013 20:09:57 +0000
Received: from mail-ee0-x22b.google.com ([2a00:1450:4013:c00::22b])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <css.liquid@gmail.com>) id 1VfEqv-00043D-RA
 for pgsql-sql@postgresql.org; Sat, 09 Nov 2013 20:09:55 +0000
Received: by mail-ee0-f43.google.com with SMTP id b47so1672526eek.30
 for <pgsql-sql@postgresql.org>; Sat, 09 Nov 2013 12:09:52 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type:content-transfer-encoding;
 bh=OFOkTnjshDXENLtCa9kXMsgB3iyhudLgaX+TObZNmvk=;
 b=SXAiNPxkGW/1zx47JUQPuHRnpCRBRJv4OFMzfjU1Y/iZHy68NYCsstX22RrdWXzbX0
 GNPZ2avmZNxT0D7infw3DZfVmK3VJaMgveJzbqbeieZ7r13VdFQR4Hzr0L0yBh7osOif
 CwxyMsy7h8gP8XwkAgucfNNP+0Yl+mDTW6H893NwNxk9dlopNM0LNCc0YOiE1fa03a3/
 VuZNVZNudSHbgio5+zgg23aCj+nRf63hUDlfyAT6Q9ZO22B4IOlpH14vlep3CEPjIkt9
 RQIiueCIydSXuO79qa5bvAEU7SerzCmiAI2yWPrJsFcacE5sU33t+Ixh4jogMA7Pq49d
 Hm8w==
X-Received: by 10.14.180.73 with SMTP id i49mr5064856eem.55.1384027792447;
 Sat, 09 Nov 2013 12:09:52 -0800 (PST)
Received: from [192.168.178.35] (byrt-4dbfe04b.pool.mediaWays.net.
 [77.191.224.75])
 by mx.google.com with ESMTPSA id b42sm40046576eem.9.2013.11.09.12.09.51
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Sat, 09 Nov 2013 12:09:51 -0800 (PST)
Message-ID: <527E968E.8010807@gmail.com>
Date: Sat, 09 Nov 2013 21:09:50 +0100
From: Michael Schmidt <css.liquid@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.0
MIME-Version: 1.0
To: "Jonathan S. Katz" <jonathan.katz@excoventures.com>
CC: pgsql-sql@postgresql.org
Subject: Re: How to script inserts where id is needed as fk
References: <527D3F2A.6060504@gmail.com>
 <05069440-C45F-4763-81A8-E14B5CB1CCB5@excoventures.com>
In-Reply-To: <05069440-C45F-4763-81A8-E14B5CB1CCB5@excoventures.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -0.1 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Am 08.11.2013 20:48, schrieb Jonathan S. Katz:
> On Nov 8, 2013, at 2:44 PM, Michael Schmidt wrote:
>
>> Hi guys,
>>
>> i need to script some insert statements. To simplify it a little bit so assume i got a
>>
>> table "User" and a table called "Article". Both tables have an serial id column. In "Articles" there is a column i need to fill with the user id lets call it "create_user_id".
>>
>> I want to do something like:
>>
>> Insert into User (name) values ('User1');
>>
>> Insert into Article ('create_user_id') values (1);
>> Insert into Article ('create_user_id') values (1);
>>
>> Insert into User (name) values ('User2');
>>
>> Insert into Article ('create_user_id') values (2);
>> Insert into Article ('create_user_id') values (2);
>>
>> So you see i have set it to 1 and 2 this not good cause it might not be 1 and 2.
>> I probably need the id returned by the "insert into User" query to use it for the "insert into Article" query.
> If you are on PG 9.1 and above, you can use a writeable CTE to do this:
>
> 	WITH users AS (
> 		INSERT INTO User (name)
> 		VALUES ('user1')
> 		RETURNING id
> 	)
> 	INSERT INTO Article ('create_user_id')
> 	SELECT id
> 	FROM users;
>
> Jonathan
>
Thanks.

This will work if i am trying to insert just one article but i have 
multiple.

To extend my scenario a little bit i have a third level called 
'Incredient' and they need the Article id. I dont think this will work 
anymore with the with clause. Any new approaches?


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql