Re: LIMITing number of results in a VIEW with global variables

On Oct15, 2011, at 14:52 , Thomas Girault wrote:
> Alternatively, we could also set the alpha value before the query :
>
> SELECT set_alpha(0.1); SELECT age, young(age) FROM employees WHERE
> young(age);

That's certainly much safer.

> I would be very interested to know if there is smarter way to set global
> variables.

The closest thing to global variables are GUC settings. These can be
set globally in postgres.conf, per user and/or per-database via
ALTER ROLE/DATABSE ... [IN DATABASE ...], per session with SET, per
function via CREATE FUNCTION ... SET and finally per subtransaction
with SET LOCAL. Modules can add their own GUC settings - you should be
able to find an example in one of the modules in contrib/

But your getter/setter-based solution isn't bad either - in fact, the
trigram module contains something very similar. Just don't try to
modify the value mid-query.

>> I can sort the results in the view 'sorted_employees' according to
>>> value MU of a fuzzy predicate thanks to fuzzy2bool cast function.
>>>
>>> CREATE OR REPLACE VIEW sorted_employees AS
>>> SELECT *, get_mu() as mu
>>> FROM employees
>>> ORDER BY mu DESC;
>>
>> Are you aware that an ORDER BY clause in a VIEW is only going to work
>> if you do "SELECT .. .FROM view".
>
> I It really the first time I am using views, I didn't know that !

Hm, I think I didn't explain that to well, so to avoid giving you a false
impression here's another try.

A SELECT without an ORDER BY clause attached to the *outmost* level never
guarantees any particular ordering of the result, nor any particular
relationship between the ordering of the SELECT's data sources and the
ordering of the SELECT's result. The only exception are SELECT's of the
form "select ... from <view>" where <view> has an ORDER BY attached to
the outmost level. From that, it follows that an ORDER BY in views used
in SELECTs more complex than the above is usually useless.

Things are different for views that combine ORDER BY and LIMIT, of course.
Then, not only the order of the view's results changes, but also it's output
set. Which of course affects every statement which uses the view.

best regards,
Florian Pflug