From: Mauricio Cruz <cruz@sygecom.com.br>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: From with case
Date: Mon, 25 Mar 2013 11:38:17 -0300
Message-ID: <3a23e70c95dc9bb82ca19be4b9abed81@sygecom.com.br> (raw)
In-Reply-To: <CAFj8pRCgqmuAD9bcty+a==EUZEFZXE9u4sJMQKvFwA4TGQDsxg@mail.gmail.com>
References: <5ba0a01548c7c6540b1c693cdf7e979d@sygecom.com.br>
<CAFj8pRCgqmuAD9bcty+a==EUZEFZXE9u4sJMQKvFwA4TGQDsxg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Thanks very much Pavel, the dynamic SQL ideia has work perfect in my
case.
Thanks so much.
--
Grato,
Mauricio Cruz
Sygecom Informática
51 3442-3975 / 3442-2345
On Mon, 25 Mar 2013 15:01:09 +0100, Pavel Stehule
<pavel.stehule@gmail.com> wrote:
> Hello
>
>
>> For rSql in select a.adiant,
>> a.desc_per
>> from case
>> when cTip='P'
>> then valapag
>> else valerec
>> end
>> where cod=2 Loop
>
>
> you can use a dynamic SQL, but it is not best solution usually. In
> this case I usually prefer
>
> IF cTip = 'P' THEN
> FOR r IN SELECT .. FROM valapag LOOP
> PERFORM proc(r);
> END LOOP;
> ELSE
> FOR r IN SELECT .. FROM valerec LOOP
> PERFORM proc(r);
> END LOOP;
> END IF;
>
> with dynamic SQL
>
> FOR r IN EXECUTE format('SELECT .. FROM %I ..', CASE WHEN ctip = 'P'
> THEN 'valapag' ELSE 'valerec' END)
> LOOP
> ..
> END LOOP;
>
> Regards
>
> Pavel Stehule
>
>
> 2013/3/25 Mauricio Cruz <cruz@sygecom.com.br>:
>> Hi everyone,
>>
>>
>>
>> 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 ?
>>
>>
>>
>> Thanks guys.
>>
>>
>>
>> --
>> Grato,
>> Mauricio Cruz
>> Sygecom Informática
>> 51 3442-3975 / 3442-2345
--
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: cruz@sygecom.com.br, pavel.stehule@gmail.com
Subject: Re: From with case
In-Reply-To: <3a23e70c95dc9bb82ca19be4b9abed81@sygecom.com.br>
* 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