Thread: CVS to In_list without dynamic SQL, how?

On Wed, Dec 9, 2015 at 1:16 PM, Michael Moore <michaeljmoore@gmail.com> wrote:

DO $$declare

   csv text := '''1'',''2'',''3''';

  v_var text;

begin

    select var into v_var from tx_vendor where vendor_key in csv;

    RAISE NOTICE 'result=(%)', v_var ;

end$$;

Obviously the above does not work, but hopefully explain what I am trying to accomplish. 

Thanks,

Mike

​SELECT '1' = ANY(string_to_array('1,2,3,4,5', ','))

David J.​

 

On Wed, Dec 9, 2015 at 4:30 PM, Michael Moore <michaeljmoore@gmail.com> wrote:

Very nice!

In my case the value being compared is numeric. I tried:
SELECT   to_char( 1 , '999') = ANY( string_to_array('1,2,3,4,5', ','));

but the result is FALSE

to_char returns a string. In this case, your spec has explicitly stated that the string must be 3 characters wide.

# select 'X' || to_char(1,'999') || 'X';

 ?column? 

----------

 X   1X

(1 row)

'1' is not 3 characters wide. '  1' <> '1'.

Skipping the forced formatting, you get the answer you want:

SELECT   1::text = ANY( string_to_array('1,2,3,4,5', ','));

 ?column? 

----------

 t

(1 row)

Similarly you do the comparison with integers, you'll get happy results:

# SELECT   1 = ANY( string_to_array('1,2,3,4,5', ',')::integer[]);

 ?column? 

----------

 t

(1 row)

 # SELECT   6 = ANY( string_to_array('1,2,3,4,5', ',')::integer[]);

 ?column? 

----------

 f

(1 row)

On Wednesday, December 9, 2015, Michael Moore <michaeljmoore@gmail.com> wrote:

Very nice!

In my case the value being compared is numeric. I tried:
SELECT   to_char( 1 , '999') = ANY( string_to_array('1,2,3,4,5', ','));

but the result is FALSE

Make the array integer to match.

string_to_array(...)::integer[]

David J.