This design has a performance problem. You read both tables everywhere -
for large tables can be bad
Dne 25.3.2013 23:51 "Ben Morrow" <ben(at)morrow(dot)me(dot)uk> napsal(a):
>
> 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
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql