One Challenging Question for PostgreSQL Beginners [PostgreSQL Tutorials]

Apart from the usual PostgreSQL tutorials, where I myself explain the concepts, here we are gonna have a look at a funny question in PostgreSQL. Here I am not providing a ready made answer, instead you have to come up with your own answer and we will choose the best answer by voting.

The Problem Statement

Here, we have two tables named, user_details and cipher_table:

CREATE TABLE user_details
(
  user_id character varying NOT NULL,
  pass_enc character varying,
  created_time timestamp with time zone,
  CONSTRAINT user_details_pkey PRIMARY KEY (user_id)
)
WITH (
  OIDS=FALSE
);
CREATE TABLE cipher_table
(
  cipher_text character varying NOT NULL,
  plain_text character varying,
  CONSTRAINT cipher_table_pkey PRIMARY KEY (cipher_text)
)
WITH (
  OIDS=FALSE
);

The user_details table contains three columns:

1. The user_id column holds the user ID of the generated users.
2. pass_enc column contains the password for the user which is the md5 encoded string of concatenation of user_id and created_time (example: md5(‘2013-11-09 04:15:11.233-08User 1’))
3. The created_time store the time of creation of the user

The cipher_table stores the password and corresponding plain text from which it is formed.

So, our challenge here is to insert 500 users dynamically with user_id like ‘User 1’, ‘User 2′,…’User 500’ and corresponding password in pass_enc (formed as explained above) and creation time in created_time column. In the same query we have to insert the passwords and corresponding plain text from which it is formed. The tables will look like below:

user_details table

cipher_table

Rules:

1. The whole insertion process must be done in a single query. No user defined functions, no more than one semi column(;)
2. The created_time must not be all same. It must be different in at least few rows.
3. No hard-coding of values except ‘User ‘, 1, 500 and  timestamp
4. Your query must be re-usable, that means, if you insert ‘User 1’ to ‘User 500’ at a time, you must also be able to insert ‘User 501’ to ‘User 1000’ using the same query.

So that’s it. Let’s see who gonna answer first!