Databases
StackOverflow2013
Postsdbo
POPostgreSQL: Can one define a session variable with the language and use it in views?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgreSQL: Can one define a session variable with the language and use it in views?
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USPascal Polleunus
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload