Message-ID: <3F3A23C5.A6E3B03B@rodos.fzk.de>
Date: Wed, 13 Aug 2003 13:40:53 +0200
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, vwu98034@lycos.com
Subject: Re: INSERT INTO ... SELECT
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> 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