Forum OpenACS Development: Re: Re: lang::util::localize in SQL

Collapse
Posted by Daniël Mantione on
Putting this function in acs-lang looks like a good idea. Below is the current version, which adds a fall-back to en_US if a translation is missing.
CREATE OR REPLACE FUNCTION localize(TEXT,TEXT) RETURNS TEXT AS '
  DECLARE lang_tag TEXT;
          s TEXT;
          package TEXT;
          key TEXT;
          r RECORD;
  BEGIN
    s := $1;
    lang_tag := substring (s FROM ''#[-a-zA-Z0-9_:\.]+\#'');
    WHILE lang_tag IS NOT NULL LOOP
        package := substring(lang_tag FROM ''#(.*)\\\\.'');
        key := substring(lang_tag FROM ''\\\\.(.*)#'');

        SELECT INTO r *
        FROM lang_messages
        WHERE message_key=key AND package_key=package AND locale=$2;
        IF r.message = '''' OR r.message IS NULL THEN
            SELECT INTO r *
            FROM lang_messages
            WHERE message_key=key AND package_key=package AND locale=''en_US'';
        END IF;

        s := replace(s,lang_tag,r.message);
        lang_tag := substring (s FROM ''#[-a-zA-Z0-9_:\.]+\#'');
    END LOOP;
    RETURN s;
  END
' LANGUAGE 'plpgsql';