functions.sql

-- Find the best suitable language for an entry
-- Returns the preferred input if it is present as a key, otherwise falls back through the list to different values
CREATE OR REPLACE FUNCTION find_language(store hstore, preferred varchar) RETURNS varchar AS
$$
SELECT CASE
           WHEN preferred = ANY (akeys(store)) THEN preferred
           WHEN 'en' = ANY (akeys(store)) THEN 'en'
           WHEN 'de' = ANY (akeys(store)) THEN 'de'
           END
$$
    LANGUAGE SQL IMMUTABLE
                 STRICT;

-- Find the actual translations for an entry
-- Returns the preferred language if it is present as a key, otherwise falls back through the list to different values
CREATE OR REPLACE FUNCTION find_translations(store hstore, preferred varchar) RETURNS varchar AS
$$
SELECT COALESCE(store -> preferred, COALESCE(store -> 'en', store -> 'de'))
$$
    LANGUAGE SQL
    IMMUTABLE
    STRICT;

get_ingredient.sql

SELECT i.id                                        AS id,
       it.id                                       AS type_id,
       find_language(i.name, :language)            AS ingredient_language,
       find_language(it.name, :language)           AS type_language,
       find_translations(it.name, :language)       AS type_name,
       find_translations(i.name, :language)        AS ingredient_name,
       find_translations(i.description, :language) AS ingredient_description
FROM ingredient i
         JOIN ingredient_type it on i.ingredient_type_id = it.id;

get_ingredient_type.sql

SELECT id,
       CASE
           WHEN :language = ANY (akeys(name)) THEN :language
           WHEN 'en' = ANY (akeys(name)) THEN 'en'
           WHEN 'de' = ANY (akeys(name)) THEN 'de'
           END                                                           AS language,
       COALESCE(name -> :language, COALESCE(name -> 'en', name -> 'de')) AS name
FROM ingredient_type;

schema.sql

CREATE EXTENSION hstore;
CREATE TABLE ingredient_type
(
    id   BIGSERIAL PRIMARY KEY,
    name HSTORE NOT NULL
);

CREATE TABLE ingredient
(
    id                 BIGSERIAL PRIMARY KEY,
    ingredient_type_id BIGINT NOT NULL REFERENCES ingredient_type (id),
    name               HSTORE NOT NULL,
    description        HSTORE NOT NULL
);
ALTER TABLE ingredient
    ADD CONSTRAINT ingredient_consistency_check CHECK (
        akeys(name) = akeys(description)
    ),
    ADD CONSTRAINT ingredient_non_empty_check CHECK (
        name <> '' AND description <> ''
    );