CODEWITHSUNDEEP
sqlrubypostgresqlsequel
Andrey Skuratovsky
Andrey Skuratovsky

Reputation: 687

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?

Upvotes: 1

Views: 974

Answers (2)

Jeremy Evans
Jeremy Evans

Reputation: 12139

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

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51529

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

Upvotes: 0

Related Questions