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

Thanks all for your help - I was able to create a view using CASE
statements that works perfectly.

Bryan

> -----Original Message-----
> From: tgl [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Saturday, March 08, 2003 4:39 PM
> To: mail
> Cc: Bryan White; pgsql-novice
> Subject: Re: [NOVICE] 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-condit
ional.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.ht
ml#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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org