>Marc Mamin-2 wrote
>> I would misuse GUC variables for this.
>> (using the functions current_setting and set_config)
>>
>> define a set get and switch fuction (I use operators for better
>> readability)
>> something like:
>>
>> select 'a' ==> 'foo'
>> 'a'
>> select 'b' <==> 'foo'
>> 'a'
>> select <== 'foo'
>> 'b'
>>
>>
>> and in your query:
>>
>> SELECT
>> case when test then col <==> 'foo' else <== 'foo' end
>
>Is it possible to alter GUC on a record-by-record basis?
yes, the underlying functions are volatile.
Which quite certainly imply that you cannot repeat the statement within a GROUP or ORDER BY...
And you must of course ensure that it get called on an ordered set.
>Is this something you have actually done?
yes
>
>Even if it does technically work this seems like a last-resort kind of
>solution. The syntax (though that could be hidden in a wrapper function) is
>definitely unusual and the abuse of the GUC system in this manner is
>surprising.
Sure. There are a some caveat too:
- it is quite slow
- you should first initialize the GUC to avoid testing it in each call.
This should be feasable with an extra function in a top WITH clause
- GUC are stored as text. You need to define functions for each type you are interested in.
This implies extra work for casting/decasting
- NULLs are transformed to empty strings per default, so you may need to care for this in your functions.
Marc Mamin
>
>David J.
>
>