Add values functions

[Sytten]

I was trying to do something similar to https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql/42217872#42217872

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )

But I don't think there is a VALUES function.

[Sytten]

Looking at it, the WRAP function will be needed for that #67

[go-jet]

Yeah, VALUES statement is not implemented yet.
SELECT can be used for one row and SELECT with UNION ALL can be used for multiple rows. For instance:

input_rows := CTE("input_rows")

stmt := WITH(
	input_rows.AS(
		UNION_ALL(
                        // first row columns has to be aliased
			SELECT(String("foo1").AS("foo"), String("bar1").AS("bar"), String("bob1").AS("bob")),
			SELECT(String("foo2"), String("bar2"), String("bob2")),
		),
	),
)(
	SELECT(input_rows.AllColumns()).
		FROM(input_rows),
)

[venimaster]

Can you use some public interface in UNION_ALL instead of private jet.SerializerStatement?
When I want to update several rows I have to write something like:

selects := make([]postgres.SelectStatement, 0, len(events))
for _, event := range events {
  // selects filling
}    
var eventsToUpdateTable postgres.SelectTable
if len(selects) == 1 {
  eventsToInsertTable = selects[0].AsTable("events_to_insert")
} else {
  set := postgres.UNION_ALL(selects[0], selects[1])
  for i := 2; i < len(selects); i++ {
    set = postgres.UNION_ALL(set, selects[i])
  }    
  eventsToInsertTable = set.AsTable("events_to_insert")
}

and I got something like union all (... union all(... union all(...)))
It would be nice to write at least:

selects := make([]postgres.SelectStatement, 0, len(events))
for _, event := range events {
  // selects filling
}
if len(selects) == 1 {
  eventsToInsertTable = selects[0].AsTable("events_to_insert")
} else if len(selects) == 2 {
  set := postgres.UNION_ALL(selects[0], selects[1])
  eventsToInsertTable = set.AsTable("events_to_insert")
} else {
  set := postgres.UNION_ALL(selects[0], selects[1], selects[2:]...)
  eventsToInsertTable = set.AsTable("events_to_insert")
}

But golang fails to use postgres.SelectStatement slice as slice of private jet.SerializerStatement.

For now I do not know any working way to update n rows in one go.

[houtn11]

Can you do it something like this - #301 (comment)?

[venimaster]

I do it this way, but what will we have in result?

union (
  select 1
  union (
    select 2
    union (
      select 3
      ...
    )
  )
)

instead of

union (
  select 1,
  select 2,
  select 3,
  ...
  select n
)

[houtn11]

There is a possibility of a small performance penalty for the huge number of rows, but the result should be the same.

[venimaster]

So why not fix it and stop adding:

1. Performance penalty.
2. Code complexity.
   ?

[go-jet]

VALUES Statements Support add in release v.2.12.0 (wiki)