Re: INSERT INTO ... SELECT

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 <ch(at)rodos(dot)fzk(dot)de>
To: pgsql-sql(at)postgresql(dot)org, vwu98034(at)lycos(dot)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