Thread: case statement as inline function?

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?
--
Mike Nolan

On Wednesday 24 March 2004 16:33, Mike Nolan wrote:
>
> Is there any way to treat it like an inline function so that I could write
> something like the following (highly simplified):

I think 7.4 will inline simple SQL functions. Your CASE looks like a good
candidate.

>    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;

--
  Richard Huxton
  Archonet Ltd

> > Is there any way to treat it like an inline function so that I could write
> > something like the following (highly simplified):
>
> I think 7.4 will inline simple SQL functions. Your CASE looks like a good
> candidate.
>
> >    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 think it may inline it as an optimization step, I don't know of any
way to shortcut how to write it.  (I'm running 7.4.1.)
--
Mike Nolan

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.