CODEWITHSUNDEEP
sqlpostgresql
Fedour
Fedour

Reputation: 387

SQL, add more informations for columns

Ok, I have a problem a bit complicated to explicate and to understand:

I have a postgreSQL DataBase who contains tables with columns, and I want to add some informations about each column, so I make a "service table" who look like this:

+---------------+-----------+--------------------+----------+----------+
| ColumnName    |  ucd      |       utype        | datatype |  table   |
+---------------+-----------+--------------------+----------+----------+
| ident         | ucd:ident | utype:EntityId     | char     | Entity   |
| label         | ucd:label | utype:EntityLabel  | char     | Entity   |
| level         | ucd:level | utype:EntityLevel  | int      | Entity   |
| idAct         | ucd:Acti  | utype:ActivityId   | char     | Activity |
| label         | ucd:label | utype:ActivityLb   | char     | Activity |
| ...           | ...       | ...                | ...      | ...      |
+---------------+-----------+--------------------+----------+----------+

The utype and ucd columns are unique

My problem is:

I do a request on my db.

Exemple: Select * from entity:

Result:

+-------+---------+-------+
| ident |  label  | level |
+-------+---------+-------+
|     1 | Entity1 |     1 |
|     2 | Entity2 |     2 |
|     3 | Entity3 |     2 |
+-------+---------+-------+

I also want to recover all the informations about my columns in my query whit my service table:

Something like this:

Ident: ucd = ucd:ident, utype = utype:EnetityId, datatype=char

Label: ucd = ucd:label, utype = utype:EntityLabel, datatype=char

Level: ucd = ucd:level, utype = utype:EntityLevel, datatype=int

I know how to have all the columns name of the result of my query, but I don't know how to have all the information about them:

I have that:

Select * from servicetable
where columnName = 'queryColumnName'

But columnName is not unique in service table so,it may return more than one row.

I hope you have understand my problem. Thank you

Upvotes: 0

Views: 60

Answers (1)

Vitor Hallais
Vitor Hallais

Reputation: 31

I really did not understand but I'll try help.

1) You already have some information about tables and columns on postgresql. Example:

SELECT *
FROM information_schema.columns
WHERE table_schema = 'your_schema'
AND table_name   = 'your_table';

2) Why dont you add one more filter like " AND table = 'Entity'" ?

Try explain more about your problem.

Upvotes: 1

Related Questions