A better way to store unknown number of attributes

Currently using Postgres 9.3 I have a Table Person(Id, FName, Lname, Address1, Adress2, phone1, phone1,....)

I could do Person(id, FName, Lname) and then Address(PersonID, AddressName, Address) and Pone(PersonID, PhoneName, Number)

But when when I need to add a new attribute, say email, I need to change the schema and add Email(PersonID, EmailName, Address)

What I want to do is Person(ID, AtrbLbl, AtribVal)

1, Fname, Ron
1, Lname, H
1, HomeEmal, rh@home.ca
1, HomeAddress, 123 st edmonton
2, LName, Smith
3, Fname, Bob
2, Fname, Sam
3, Lnaem, Marly
3, HomeAdress, Heven
2, HomeAddress, abc St.
1, FavorateColor, red
2, FavorateColor, red
3, FavorateColor, red
1, FavorateIcream, Chocolate 
2, FavorateIcream, Vanila
3, FavorateIcream, Mint
4, FName, tom
4, FavorateColor, blue

Where I, Ron H, am made up of all id = 1 and if, say I got a job you could add 1, WorkEmail, rh@Work.ca

So if I want all the attributes of everyone who's FavorateColor is red

Select * from person where id in (Select ID from person where  AtrbLbl = FavorateColor and AtribVal = red)`

My problem is search more than one attribute. In sudo sql what I want is

Select * from person where id in (Select id from person where (AtrbLbl = FavorateColor and AtribVal = red) AND (AtrbLbl = Fname and AtribVal = Ron)

Obviously that won't work.

What I was thinking of doing is

insert into temptbl
Select Count(id) cnt, ID from person where (AtrbLbl = FavorateColor and AtribVal = red) OR (AtrbLbl = Fname and AtribVal = Ron) 

Select * From person where id in (select id from temtbl where cnt = 2)  order by id
where 2 is the number of searched attributes.

So if I wanted the persons who like red, Chocolate and FName Ron

insert into temptbl
Select Count(id) cnt, ID from person where (AtrbLbl = FavorateColor and AtribVal = red) OR (AtrbLbl = Fname and AtribVal = Ron) OR (AtrbLbl = FavorateIcream and AtribVal = Chocolate) 

Select * From person where id in (select id from temtbl where cnt = 3)  order by id

In my mind I should be able to do this in on statement by joining the results from one part of the where to the results of another part.

Can anyone think of a single statement that can do this? Or a more elegant method?

Solution

Classic SQL works better with static schema.

Still, it is possible to write a single query in your case.

For example, you want to find all people who have:

FavorateColor = red
AND
Fname = Ron
AND
FavorateIcream = Chocolate

Do three separate queries for each attribute and return only those IDs that match all three filters:

SELECT *
FROM PersonDetails
WHERE PersonID IN
    (
        SELECT ID
        FROM person
        WHERE AtrbLbl = 'FavorateColor' AND AtribVal = 'red'

        INTERSECT

        SELECT ID
        FROM person
        WHERE AtrbLbl = 'Fname' AND AtribVal = 'Ron'

        INTERSECT

        SELECT ID
        FROM person
        WHERE AtrbLbl = 'FavorateIcream' AND AtribVal = 'Chocolate'
    )

So, it is possible, but, I personally, would not do it. I would have separate tables for People, Addresses, Phones, Emails, just as you described in the beginning of your question.