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.
