Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.8.8/8.8.8) with ESMTP id MAA16851 for ; Mon, 28 Sep 1998 12:20:38 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id SAA04485 for ; Mon, 28 Sep 1998 18:20:11 +0200 (IST) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <20074.906991330@sss.pgh.pa.us> References: Your message of Sun, 27 Sep 1998 23:00:21 -0400 (EDT) <199809280300.XAA18545@hub.org> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 28 Sep 1998 18:20:54 +0200 To: pgsql-sql@hub.org From: Herouth Maoz Subject: Re: [SQL] Re: Another nut to crack with insertion into tables... *sigh* At 16:02 +0200 on 28/9/98, Tom Lane wrote: > > I believe sub-selects are currently only supported in WHERE clauses. > Supporting them elsewhere is on the TODO list for a future release > (no, 6.4 won't have it). In the meantime you'll have to read back > the result of the sub-select and include it in the text of the INSERT. > > (I'm not sure whether the SQL spec allows a select in an INSERT like this > anyway. What happens if the sub-select returns no tuples, or more than > one tuple?) First, there is an exception which is supposed to be thrown in such a case. But anyway, there is no reason to do things like this. Read the "insert" manpage. The syntax is: insert into classname [(att.expr-1 [,att_expr.i] )] {values (expression1 [,expression-i] ) | select expression1 [,expression-i] [from from-list] [where qual] So, you should format your insert like this: INSERT INTO my_table (int_field, text_field, another_int_field, another_text_field ) SELECT 15, 'The Wind in the Willows', foreign_int_field, foreign_text_field FROM foreign_table WHERE ... Everything which you want inserted literally, you put as constants on the select list. It's a valid expression. Anything you want from the other table (or tables!), you mention by its field name. Very simple, and has been working for ages. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma