Question 176313 in PostgreSQL Question posted in
The official documentation can be found here.

What is the Postgresql equivalent of SQL Server’s CHOOSE and STRING_SPLIT functions?

So optimized a sql server proc recently. Went from this

select id, 'col1' as valtype, col1 as val from table1 where col1 > 0
union all
select id, 'col2', col2 from table1 where col2 > 0
union all
select id, 'col3', col3 from table1 where col3 > 0

to this:

select * from (
  select id, valtype,
    case valtype
      when 'col1' then col1
      when 'col2' then col2
      when 'col3' then col3
    end as val
  from table1
  cross join (VALUES('col1'),('col2'),('col3')) t(valtype)
) a where val > 0

to this:

select * from (
  select id, value as valtype,
    choose(ordinal, col1, col2, col3) as val
  from table1
  cross join string_split('col1,col2,col3', ',', 1) t
) a where val > 0

So how would I go about this progression in Postgres?

Is there an equivalent function to CHOOSE other than CASE?

Is there a string_to_array that also returns index?

Tags:

2

Answers


  1. 0

    I don’t think there is a postgresql equivalent of CHOOSE(), however something like string_split can be done with unnest with ordinality and string_to_array like follows:

    select * from (
  select id, valtype,
    case valtype
      when 'col1' then col1
      when 'col2' then col2
      when 'col3' then col3
    end as val
  from table1
  cross join unnest(string_to_array('col1,col2,col3', ',') ) WITH ORDINALITY a(valtype, nr)
) a where val > 0
    Login or Signup to reply.
  2. 0

    If you find the choose() function convenient and desirable, you can easily create it in Postgres:

    create or replace function choose(bigint, variadic anyarray)
returns anyelement language sql immutable as $$
    select $2[$1]
$$;

    Instead of string_split() use string_to_table() with ordinality:

    select * 
from (
    select 
        id, value as valtype,
        choose(ordinal, col1, col2, col3) as val
    from table1
    cross join string_to_table('col1,col2,col3', ',') with ordinality as t(value, ordinal)
    ) a 
where val > 0
order by 1, 2

    Test it in db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search