Reading a column value from more than one table based on some condition - postgreSQL

I am trying to create a view for one of the tables I have. For one of the columns in the view, I am trying to read the value from the tables I have:

Table A:

id   b_id   c_id    d_id
1     1
2            1
3                     1

Table B:

id   code
1     64

Table C:

id    code
1     98

Table D:

id     code
1       26

In the above tables, A is my main table which has two foreign key references to tables B and C(b_id and c_id) respectively. And for each row in table A, only one of the two references (b_id or c_id) will be populated.

My task is to create a view for table A, say v_A, which will have the following columns:

View v_A:

code      code_table
64           B
98           C
26           D

In the above view, I have to first check for each row in table A, which of the foreign key references are populated, based on that I have to read the column 'code' and the column 'code_table' will have the name of the table I am populating the 'code' column.

I have been able to create simple views but this is a bit tricky for me. I was checking if I can use case ...when for the same but after a point that became a bit complicated too. I also can read the table name in postgres from the information_schema table. So I have bits and pieces of the solution but do not understand how to put it all together.

Can someone point me in the right direction?

Thanks!!

Solution

Of course it will work only when you have EITHER one table filled OR the OTHER - it does not solve conflict when both tales have matching keys:

t=# create view _a as select coalesce(b.code,c.code,d.code) code, case when b.id is not null then 'B' when c.id is not null then 'C' when d.id is not null then 'D' end from a
left outer join b on b.id = b_id
left outer join c on c.id = c_id
left outer join d on d.id = d_id;
CREATE VIEW
t=# select * from _a;
 code | case
------+------
   26 | D
   98 | C
   64 | B
(3 rows)