Joe Conway <mail@joeconway.com> writes:
> bryan@flyingiranch.com wrote:
>> Next question: One of the reasons a function is attractive to me in this
>> situation is that I also have some conditionals to handle. For instance,
>> Base Metabolic Rate is different whether you are male or female (gender
>> is a boolean value in my table). Can I use IF/THEN syntax in a view
>> definition?
> Take a look at the CASE conditional expression:
> http://developer.postgresql.org/docs/postgres/functions-conditional.html
Also, if your needs go beyond what seems reasonable to wedge into a
CASE, you could define a view that uses a function. For example,
CREATE FUNCTION calc_bmi(basetable) returns float8 as
'compute appropriate value from fields of $1' ...;
CREATE VIEW derivedtable AS
SELECT *, calc_bmi(basetable) FROM basetable;
Passing in the whole row isolates the view definition from needing to
know exactly which fields go into the BMI calculation. See
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-sql.html#AEN31256
for discussion of this. As of recent versions you can also say
SELECT *, calc_bmi(basetable.*) FROM basetable;
which might or might not seem clearer to you...
regards, tom lane
