Re: From with case

Quoth cruz@sygecom.com.br (Mauricio Cruz):
> 
> I'm working in a PL/SQL and I'd like to use the same
> PL for 2 kinds of tables... 
> 
> I have "valepag" and "valerec" both tables
> have the same columns, but one is for debit and the other one is for
> credit, the PL will work for both cases 
> 
> with the unique diference for
> the name of the table... 
> 
> So I thought to use something like this:
> ... 
> 
> For rSql in select a.adiant,
>  a.desc_per
>  from case
>  when
> cTip='P'
>  then valapag
>  else valerec
>  end
>  where cod=2 Loop 
> 
> ... 
> 
> But
> it just dont work... does some one have other solution for this case ?

I would use a view for this:
   create view vale_any as   select 'P'::text "type", v.adiant, v.desc_per, v.cod   from valepag v   union all   select
'R',v.adiant, v.desc_per, v.cod   from valerec v;
 

then
   for rSql in       select a.adiant, a.desc_per       from vale_any a       where a.type = cTip and a.cod = 2   loop

You need to cast the constant in the view definition, otherwise Pg
complains about its type being ambiguous. You should use the same type
as cTip will be.

Ben