Search code examples
sqlrubypostgresqlsequel

Ruby Sequel select from values

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?

Solution

  • 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")