On Wed, Feb 3, 2016 at 1:18 PM, arnaud gaboury <arnaud.gaboury@xxxxxxxxx> wrote: > $ 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/ > EDIT: I deleted my first INSERT in table email.mailusers this way: DELETE * FROM email.mailusers , and now the id is set to 2. I would prefer having the id reset to 1. What is the correct way to get back id to 1 ? -- google.com/+arnaudgabourygabx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
