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 <[EMAIL PROTECTED]> To: [EMAIL PROTECTED], [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
