Re: Stored procedure - change columns in a table that is

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