Re: From with case - Mailing list pgsql-sql

From Pavel Stehule
Subject Re: From with case
Date
Msg-id CAFj8pRCzVM7waFKX+rnYmYST+Z4yL0UVwyAUs=cX4BDV1LFK4Q@mail.gmail.com
Whole thread Raw
In response to Re: From with case  (Ben Morrow <ben@morrow.me.uk>)
Responses Re: From with case
List pgsql-sql
<p dir="ltr">This design has a performance problem. You read both tables everywhere - for large tables can be bad<br />
Dne25.3.2013 23:51 "Ben Morrow" <<a href="mailto:ben@morrow.me.uk">ben@morrow.me.uk</a>> napsal(a):<br /> ><br
/>> Quoth <a href="mailto:cruz@sygecom.com.br">cruz@sygecom.com.br</a> (Mauricio Cruz):<br /> > ><br /> >
>I'm working in a PL/SQL and I'd like to use the same<br /> > > PL for 2 kinds of tables...<br /> > ><br
/>> > I have "valepag" and "valerec" both tables<br /> > > have the same columns, but one is for debit and
theother one is for<br /> > > credit, the PL will work for both cases<br /> > ><br /> > > with the
uniquediference for<br /> > > the name of the table...<br /> > ><br /> > > So I thought to use
somethinglike this:<br /> > > ...<br /> > ><br /> > > For rSql in select a.adiant,<br /> > >
 a.desc_per<br/> > >  from case<br /> > >  when<br /> > > cTip='P'<br /> > >  then valapag<br
/>> >  else valerec<br /> > >  end<br /> > >  where cod=2 Loop<br /> > ><br /> > > ...<br
/>> ><br /> > > But<br /> > > it just dont work... does some one have other solution for this case
?<br/> ><br /> > I would use a view for this:<br /> ><br /> >     create view vale_any as<br /> >    
select'P'::text "type", v.adiant, v.desc_per, v.cod<br /> >     from valepag v<br /> >     union all<br /> >  
 select 'R', v.adiant, v.desc_per, v.cod<br /> >     from valerec v;<br /> ><br /> > then<br /> ><br />
>    for rSql in<br /> >         select a.adiant, a.desc_per<br /> >         from vale_any a<br /> >      
 where a.type = cTip and a.cod = 2<br /> >     loop<br /> ><br /> > You need to cast the constant in the view
definition,otherwise Pg<br /> > complains about its type being ambiguous. You should use the same type<br /> > as
cTipwill be.<br /> ><br /> > Ben<br /> ><br /> ><br /> ><br /> > --<br /> > Sent via pgsql-sql
mailinglist (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> > To make changes to your
subscription:<br/> > <a
href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a><br/> 

pgsql-sql by date:

Previous
From: Ben Morrow
Date:
Subject: Re: From with case
Next
From: Ben Morrow
Date:
Subject: Re: From with case