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

Joe Conway <mail(at)joeconway(dot)com> writes:
> bryan(at)flyingiranch(dot)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