Re: case statement as inline function?

hi,

Mike Nolan wrote:

> Periodically I need to write a complex case statement that I'd like to
> be able to refer to in more than one place in a SQL command without having
> to make sure that each copy of the case statement remains the same as
> the query (to produce a mailing) is tailored.
>
> Is there any way to treat it like an inline function so that I could write
> something like the following (highly simplified):
>
>    select case when A=1 then 1 when B=1 then 2 else null end
>    as mailtype, memname from master
>    where mailtype is not null;
>
> I could do it as a user function, though that would be less convenient during
> the specification phase, which may happen every few days.  However, the
> columns referred to in the case statement can change too.  Is there a way
> to pass the entire set of columns in a table to a function?

you can use array as parameter, than walk the array inside plpgsql, or
c, plperl ...

A simple plpgsql:
create or replace function _a(varchar[]) returns varchar as '
declare
t alias for $1;
i integer default 1;
s varchar default ''case when '';
begin
while t[i][1] <> '''' loop
s:=s||t[i][1];
if i%2<>0 then
  s:=s||''='';
end if;
i:=i+1;
if t[i][1]<>'''' and i%2<>0 then
  s:=s||'' then '';
end if;
end loop;
return s||'' end'';
end;
'language plpgsql immutable;

SELECT _a(ARRAY[['1'],['2'],['3'],['4']]);
                                                        _a
----------------------------
  case when 1=2 then 3=4 end

this is not usefull for using in select in this case, just show, how to
work with array :)

C.