title: PostgreSQL: Can one define a session variable with the language and use it in views?
body: <p>Here's a simplified example of schema:</p>
  
  <pre><code>Table l10n ( l10n_id SMALLINT, code VARCHAR(5) )  
  Table product ( product_id INT, ..language-neutral columns.. )  
  Table product_l10n ( product_id INT, l10n_id SMALLINT, ..language-specific columns.. )</code></pre>
  
  <p>Querying for products with localized data is done like this:</p>
  
  <pre><code>    SELECT *
      FROM product a
          LEFT JOIN product_l10n b ON b.id = a.id
          LEFT JOIN l10n c ON c.id = b.id
      WHERE c.code = 'en-US';</code></pre>
  
  <p>To avoid that big fat query, I would like to use views.<br>
  The basic idea is to create a view based on the above query without the where clause.<br>
  Querying for products would then become:</p>
  
  <pre><code>    SELECT * FROM product_view WHERE c.code = 'en-US';</code></pre>
  
  <p>Another idea would be to have a variable containing the language tag, defined for each DB connection/session.<br>
  The view would be based on the first query using the variable in the where clause.<br>
  The variable being set in the current DB session, querying for products would then be as simple as this:</p>
  
  <pre><code>    SELECT * FROM product_view;</code></pre>
  
  <p><strong>So my question is: Can one do that? How?</strong></p>
  
  <p>It's feasible using custom variables in postgresql.conf. See the doc <a href="http://www.postgresql.org/docs/8.3/interactive/runtime-config-custom.html" rel="nofollow noreferrer">Customized Options</a>.</p>
  
  <p>In postgresql.conf:</p>
  
  <pre><code>    custom_variable_classes = 'myproject'
      myproject.l10n_id = 'en-US'</code></pre>
  
  <p>At the beginning of the DB session (the param is set at session level by default):</p>
  
  <pre><code>    SET myproject.l10n_id = 'en-US';</code></pre>
  
  <p>In views:</p>
  
  <pre><code>    WHERE c.code = current_setting('myproject.l10n_id')</code></pre>
  
  <p><strong>But... I don't like having to define a variable for the whole server. Is there a way to achieve the same but on a per database basis?</strong></p>
  
  <p>Thanks in advance,<br>
  Pascal</p>
  
  <p>PS: I've postes another question regarding using l10n_id as SMALLINT or directly as the ISO code in a VARCHAR(5). See http:// stackoverflow.com /questions/1307087/how-to-store-language-tag-ids-in-databases-as-smallint-or-varchar <em>(sorry, only 1 URL for new users :-)</em></p>