Re: From with case - Mailing list pgsql-sql

From Mauricio Cruz
Subject Re: From with case
Date
Msg-id 3a23e70c95dc9bb82ca19be4b9abed81@sygecom.com.br
Whole thread Raw
In response to Re: From with case  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-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




pgsql-sql by date:

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