Language dependent column headers

Question

I am working on an PostgreSQL based application and am very curious if there might be a clever solution to have language dependent column headers.

I sure know, that I can set an alias for a header with the "as" keyword, but that obviously has to be done for every select and over and over again.

So I have a table for converting the technical column name to a mnemonic one, to be shown to the user.

I can handle the mapping in the application, but would prefer a database solution. Is there any?

At least could I set the column header to table.column?

score 0 · Answer 1 · answered Jul 23 '21 at 15:55

You could use a "view". You can think of a view as a psuedo-table, it can be created using a single or multiple tables created from a query. For instance, if I have a table that has the following shape

Table: Pets

Id | Name | OwnerId | AnimalType
1  | Frank| 1       | 1
2  | Jim  | 1       | 2
3  | Bobo | 2       | 1

I could create a "view" that changes the Name field to look like PetName instead without changing the table

CREATE VIEW PetView AS
    SELECT Id, Name as PetName, OwnerId, AnimalType
    FROM Pets

Then I can use the view just like any other table

SELECT PetName 
FROM PetView
WHERE AnimalType = 1

Further we could combine another table as well into the view. For instance if we add another table to our DB for Owners then we could create a view that automatically joins the two tables together before subjecting to other queries

Table: Owners

Id | Name
1  | Susan
2  | Ravi

CREATE VIEW PetsAndOwners AS
    SELECT p.Id, p.Name as PetName, o.Name as OwnerName, p.AnimalType
    FROM Pets p, Owners o
    WHERE p.OwnerId = o.Id

Now we can use the new view again as in any other table (for querying, inserts and deletes are not supported in views).

SELECT * FROM PetsAndOwners
WHERE OwnerName = 'Susan'

Language dependent column headers

1 Answers1