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
seemto have a single query solution.
v.
--
--------- Original Message ---------
DATE: Wed, 13 Aug 2003 13:40:53
From: Christoph Haller <ch@rodos.fzk.de>
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
