X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 813FDD1C4D0 for ; Wed, 13 Aug 2003 17:14:50 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 48799-03 for ; Wed, 13 Aug 2003 14:13:14 -0300 (ADT) Received: from lycos.com (www1.mail.lycos.com [209.202.220.140]) by svr1.postgresql.org (Postfix) with SMTP id 4C8FDD1B56C for ; Wed, 13 Aug 2003 14:14:39 -0300 (ADT) Received: from Unknown/Local ([?.?.?.?]) by mailcity.com; Wed, 13 Aug 2003 17:14:28 -0000 To: pgsql-sql@postgresql.org, "Christoph Haller" Date: Wed, 13 Aug 2003 13:14:28 -0400 From: "Vernon Smith" Message-ID: Mime-Version: 1.0 X-Sent-Mail: off Reply-To: vwu98034@lycos.com X-Mailer: MailCity Service X-Priority: 3 Subject: Re: INSERT INTO ... SELECT X-Sender-Ip: 24.87.63.31 Organization: Lycos Mail (http://www.mail.lycos.com:80) Content-Type: text/plain; charset=us-ascii Content-Language: en Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.7 tagged_above=0.0 required=5.0 tests=BAYES_20, FROM_ENDS_IN_NUMS, MAILTO_TO_SPAM_ADDR, QUOTED_EMAIL_TEXT, RCVD_FAKE_HELO_DOTCOM X-Spam-Level: * X-Archive-Number: 200308/145 X-Sequence-Number: 14761 Thanks, Christoph for your thought. An alternative solution I have is to fetch the user table first and act according with the retured value. It doesn't seem to have a single query solution. v. -- --------- Original Message --------- DATE: Wed, 13 Aug 2003 13:40:53 From: Christoph Haller To: pgsql-sql@postgresql.org, vwu98034@lycos.com Cc: >> >> Coincidentally, I have a very similar case using some twists. >> >> The table I want to insert data is something like >> >> table A ( >> user01 int, >> user02 int, >> ... >> primary key (user01, user02), >> CHECK ( user01 < user02 ) >> ); >> >> And the user table is: >> >> tabe user_table ( >> user int constraint pk_user primary key UNIQUE, --PK >> ..., >> email varchar(25) not null, >> ... >> }; >> >> In the insertion statement, the user02 is obtained by a subselect >statement: "select user from user where email=?". >> >> There may not exist the email in the user table. And if yes, the check >condition may not be met. If the condition is n >ot met, the two user IDs need to be switched. >> >> How the query shall be construted? >> >> Thanks for any suggestions. >> > >I'd say this conditional insert into user02 column can be done by a >PL/pgSQL function >combined by CASE statements. >I'm thinking of something like (untested of course) >INSERT INTO ... >SELECT >CASE WHEN user01 < get_user02(email=?) >THEN user01 ELSE get_user02(email=?) END, >CASE WHEN user01 < get_user02(email=?) >THEN get_user02(email=?) ELSE user01 END, >... >FROM table A ... ; > >You might think calling the function four times is a waste, >but as far as I understand it PL/pgSQL functions are run via >prepared execution plans (see PL/pgSQL - SQL Procedural Language - >Overview), >so it shouldn't be too bad. >Don't know what to do about >> There may not exist the email in the user table. >Insert a NULL? > >HTH > >Regards, Christoph > > > ____________________________________________________________ Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005