Re: From with case

Quoth cruz(at)sygecom(dot)com(dot)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