From: Ben Morrow <ben@morrow.me.uk>
To: cruz@sygecom.com.br
To: pgsql-sql@postgresql.org
Subject: Re: From with case
Date: Mon, 25 Mar 2013 22:50:40 +0000
Message-ID: <20130325225034.GA73919@anubis.morrow.me.uk> (raw)
In-Reply-To: <5ba0a01548c7c6540b1c693cdf7e979d@sygecom.com.br>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ben@morrow.me.uk, cruz@sygecom.com.br
Subject: Re: From with case
In-Reply-To: <20130325225034.GA73919@anubis.morrow.me.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox