SQL Function with variadic varchar input and variadic integer output

Question

I know I could do the following with a inner join and a select with a correspondence table, but would rather have a function.

Let's says I have a query such as select * from foobar where is_valid in ('yes', 'no'). Unfortunately foobar.is_valid is an integer, 0 or 1 only.

I'ld like to create a function that maps the varchar to integers, something like select * from foobar where is_valid in fn_yesno_to_int('yes', 'no'). Is this even possible?

I'm not at liberty to change the in clause to something else, nor am I at liberty to call the function for each value in the in clause (no in (func('yes'), func('no'))).

My last resort would be to create a correspondence table such as {{0, 'no'}, {1, 'yes'}} and use it and drop the idea of using a function. Another, probably better, option would be to flip the issue around and call the function on foobar.is_valid mapping the 0,1 to no,yes; but I'm very curious to know if the function taking an array and returning an array in my case is even possible.

No trivially. You can do this using arrays, but it seems like a lot of work for something that can be done pretty trivially using other methods. — Gordon Linoff
– Gordon Linoff, Commented Apr 26, 2018 at 11:24
@a_horse_with_no_name yes. Which is alexey-bashtanov 's second solution. — Julien
– Julien, Commented Apr 26, 2018 at 13:31

Alexey Bashtanov · Accepted Answer · 2018-04-26 11:37:18Z

1

I don't think it is possible to write a function that makes the syntax where is_valid in fn_yesno_to_int('yes', 'no') valid.

However, you can write a function that can be used like where is_valid in (select * from fnyn1('yes', 'no')) or like where is_valid = any(fnyn2('yes', 'no')):

CREATE function fnyn1(variadic labels text[] default '{}') returns setof int as $$
    select column1
    from (values(0, 'no'),(1, 'yes'))_
    join unnest(labels) u on column2 = u;
$$ language sql;

CREATE function fnyn2(variadic labels text[] default '{}') returns int[] as $$
    select array(
        select column1
        from (values(0, 'no'),(1, 'yes'))_
        join unnest(labels) u on column2 = u
    );
$$ language sql;

edited Apr 26, 2018 at 11:37

user330315

answered Apr 26, 2018 at 11:36

Alexey Bashtanov

1,40411 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

SQL Function with variadic varchar input and variadic integer output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related