Thread: using case to select 'which version to use'
Hi all. I have 2 functions , which returns the same columns, but with tottaly different logics. I want to wrap it with a third function, who will receive a boolean argument to decide wich sub-function have to use. I want to use case, so i dont have to use pl/pgsql. This is a non-working example: CREATE OR REPLACE FUNCTION get_oferta(varchar, varchar, varchar, bool, OUT cod varchar , OUT description varchar) returns setof record security definer as $$ select * from case $4 when true then (select * from get_oferta_from_a($1, $2, $3)) else (select * from get_oferta_from_b($1, $2, $3)) end; $$ language sql; Can i use case for a case like this? Thanks! Gerardo
On Thu, Aug 20, 2009 at 10:17 AM, Gerardo Herzig<gherzig@fmed.uba.ar> wrote: > Hi all. I have 2 functions , which returns the same columns, but with > tottaly different logics. I want to wrap it with a third function, who > will receive a boolean argument to decide wich sub-function have to use. > > I want to use case, so i dont have to use pl/pgsql. > > This is a non-working example: > > > CREATE OR REPLACE FUNCTION get_oferta(varchar, varchar, varchar, bool, > OUT cod varchar , OUT description varchar) > returns setof record > security definer > as > $$ > select * from case $4 when true then > (select * from get_oferta_from_a($1, $2, $3)) > else > (select * from get_oferta_from_b($1, $2, $3)) > end; > $$ language sql; > > Can i use case for a case like this? > > Thanks! > Gerardo Try: SELECT * FROM get_oferta_from_a ($1, $2, $3) WHERE $4 UNION ALL SELECT * FROM get_oferta_from_b ($1, $2, $3) WHERE NOT $4;
Rodrigo E. De León Plicet wrote: > On Thu, Aug 20, 2009 at 10:17 AM, Gerardo Herzig<gherzig@fmed.uba.ar> wrote: >> Hi all. I have 2 functions , which returns the same columns, but with >> tottaly different logics. I want to wrap it with a third function, who >> will receive a boolean argument to decide wich sub-function have to use. >> >> I want to use case, so i dont have to use pl/pgsql. >> >> This is a non-working example: >> >> >> CREATE OR REPLACE FUNCTION get_oferta(varchar, varchar, varchar, bool, >> OUT cod varchar , OUT description varchar) >> returns setof record >> security definer >> as >> $$ >> select * from case $4 when true then >> (select * from get_oferta_from_a($1, $2, $3)) >> else >> (select * from get_oferta_from_b($1, $2, $3)) >> end; >> $$ language sql; >> >> Can i use case for a case like this? >> >> Thanks! >> Gerardo > > Try: > > SELECT * > FROM get_oferta_from_a ($1, $2, $3) > WHERE $4 > UNION ALL > SELECT * > FROM get_oferta_from_b ($1, $2, $3) > WHERE NOT $4; > > Well, looks like it will work. I think i found the right syntax now: select foo.* from (select case $4 when false then get_oferta_from_a($1, $2, $3) else get_oferta_from_b($1, $2, $3)end) as foo; Thanks! Gerardo