Ruby Sequel select from values

Question

I need to execute sql like:

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter) ...;

using sequel syntax.

I need something like that:

two_dimesional_array = [[1, "one"], [2, 'two'], [3, 'three']]
DB["SELECT * FROM (VALUES ?)", two_dimesional_array ]

Reason why - i have a list of data, and i need to select records which not exists in database table.

This example works:

DB["SELECT * FROM unnest(?) EXCEPT ALL SELECT out_id FROM my_table", Sequel.pg_array(ids)]

it works well, but i need to create table with 2 columns from my data. Function unnest create single column table.

UPDATE:

The best solution i found for now is to use sequel sql_value_list

values = DB.literal(
        records.map{|e| [e['one'], e['two']]}.sql_value_list
).sub(/^\(/, '(VALUES ')
sql = "SELECT * FROM #{values} ..."

It's not pretty decision, because sql_value_list generates list in outer brackets without word VALUES and i need open literal and substitute this word.

Maybe there is more elegant way?

Jeremy Evans · Accepted Answer · 2018-02-12 18:46:16Z

3

Sequel has native support for VALUES:

DB[DB.values([[1, 'one'], [2, 'two'], [3, 'three']]).as(:t, [:num, :letter])]
# SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS "t"("num", "letter")

answered Feb 12, 2018 at 18:46

Jeremy Evans

12.2k31 silver badges27 bronze badges

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

1 Comment

Andrey Skuratovsky Over a year ago

Thanks! That's what I was looking for!

Vao Tsun · Accepted Answer · 2018-02-10 22:03:27Z

0

I have one ugly way of casting 2d array to rows:

t=# with c(a) as (values('[[1, "one"], [2, "two"], [3, "three"]]'::jsonb))
, u as (select translate(jsonb_array_elements_text(a)::text,'[]','{}')::text[]::text::text[] ugly from c)
select ugly[1] u1,ugly[2] u2 from u;
 u1 |  u2
----+-------
 1  | one
 2  | two
 3  | three
(3 rows)

Note that I changed single quotes to double to be valid json. I'm sure there is more elegant solution, but not tonight

answered Feb 10, 2018 at 22:03

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Ruby Sequel select from values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related