$ psql -V
psql (PostgreSQL) 9.4.5
I am quite a newbie in psql. I am setting an email server and need to
create then query psql tables to get some information: mainly email
address and mail directory.
For now I have created two tables this way. Both tables are in same
database and schema. I only insert one row in each.
1- the first one is just a list of all my domains.
-----------------------------------------
CREATE TABLE email.domain (
id SERIAL,
domain TEXT NOT NULL,
PRIMARY KEY (id)
);
---------------------------------
2- second is a list of users
--------------------------------------------------
CREATE TABLE email.mailusers (
id SERIAL PRIMARY KEY,
domain_id INTEGER DEFAULT 1, <<-- This ref to email.domain id=1
password TEXT NOT NULL,
username TEXT UNIQUE NOT NULL,
created TIMESTAMP WITH TIME ZONE DEFAULT now();
------------------------------------
3- Then I added a constraint:
---------------------------------
ALTER TABLE email.mailusers
ADD CONSTRAINT mailuser_domain_id_fkey
FOREIGN KEY (domain_id)
REFERENCES email.domain(id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
-------------------------------------
Then I need to retrieve from psql to postfix this parameter value:
- email adress .
The email address is obviously something like a a concat (username,'@',domain).
My first idea is to create a view (or materialized view ?) following
the principle described here[1]
create view postfix_virtual as
select userid, userid as address from users
union all
select userid, address from virtual;
, but I am a little lost when it comes to the UNION stuff (shall I use it ?).
Second possibility would be to create a modified query similar to this[2]:
query = SELECT concat(username,'@',domain) as email FROM users WHERE
username='%s'
From now, I am able to create a view like this:
---------------------------------------------
CREATE VIEW email_address AS
SELECT * FROM
(SELECT username
FROM email.mailusers
WHERE id=2)a,
(SELECT domain
FROM email.domain
WHERE id=1)b;
---------------------------------
I get a table:
username | domain
---------------------+--------------------
myuser.name | mydomain.com
That's fine, but it is far from being generic and satisfying.
Thank you for help and advises.
[1]http://wiki2.dovecot.org/HowTo/DovecotPostgresql/
[2]https://blog.za3k.com/installing-email-with-postfix-and-dovecot/
google.com/+arnaudgabourygabx