Date: Mon, 25 Mar 2013 22:50:40 +0000
From: Ben Morrow <ben@morrow.me.uk>
To: cruz@sygecom.com.br, pgsql-sql@postgresql.org
Subject: Re: From with case
Message-ID: <20130325225034.GA73919@anubis.morrow.me.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <5ba0a01548c7c6540b1c693cdf7e979d@sygecom.com.br>
Organization: morrow.me.uk
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql