From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
To: vwu98034@lycos.com
Subject: Re: INSERT INTO ... SELECT
Date: Wed, 13 Aug 2003 13:40:53 +0200
Message-ID: <3F3A23C5.A6E3B03B@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, vwu98034@lycos.com
Subject: Re: INSERT INTO ... SELECT
In-Reply-To: <3F3A23C5.A6E3B03B@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox