Conditional Statements in PostgreSQL

Question

A 'select' statement produce some sequence of data output like 'SL' 'CL''PL' etc as shown in below picture

If the data output is SL then it should be converted and displayed as 'Z', if its 'CL' the 'Q', if its 'PL' then 'R'

Is there any way to implement this

meta.stackoverflow.com/questions/285551/…

user330315
– user330315

2016-10-19 05:58:02 +00:00
Commented Oct 19, 2016 at 5:58 — user330315
– user330315, Commented Oct 19, 2016 at 5:58

ChrisB · Accepted Answer · 2016-10-19 05:32:52Z

2

select case when dbreed1 = 'SL' then 'Z'
            when dbreed1 = 'CL' then 'Q'
            when dbreed1 = 'PL' then 'R'
            else 'other'
       end dbreed1_mapped

You can also write your own function:

create or replace function map_breed(_input text) returns text as
$$
    select case when _input = 'SL' then 'Z'
                when _input = 'CL' then 'Q'
           end
$$
language sql;

And call it like this:

select map_breed('SL');

edited Oct 19, 2016 at 5:32

answered Oct 19, 2016 at 5:19

ChrisB

2,6152 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mr.Tananki Over a year ago

I could but is there any better way than that, like 'for' loop for the whole columns? so that no need to write for individual one

ChrisB Over a year ago

@Mr.Tananki I edited my answer so you only have to do the mapping once in the function

Mr.Tananki Over a year ago

writing own function worked after struggling to handel the following error.

Mr.Tananki Over a year ago

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "map_breed" line 2 at SQL statement

Mr.Tananki · Accepted Answer · 2016-10-20 06:12:32Z

0

create or replace function map_breed(dbreed text) returns text as 
$$ 
   begin 
   select case when dbreed = 'SL' then 'Z' 
               when dbreed = 'CL' then 'Q' end into dbreed; 
   return dbreed;
   end; 
$$ language plpgsql; 

/*and call the function as*/
select map_breed('SL') as breed

answered Oct 20, 2016 at 6:12

Mr.Tananki

4891 gold badge6 silver badges11 bronze badges

1 Comment

user330315 Over a year ago

A plain SQL function would be more efficient for this

Collectives™ on Stack Overflow

Conditional Statements in PostgreSQL

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related