Support for VALUES List

[bmyzk]

Does pypika support for VALUES List like below?
If not, it would be great to add a class for it.

https://www.postgresql.org/docs/9.5/queries-values.html
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);

[bmyzk]

Here is my workaround.

class ValueList(Selectable):
    def __init__(self, value_list, table_name, alias=None):
        super().__init__(alias=alias)
        self.value_list = value_list
        self.table_name = table_name

    def get_table_name(self):
        return self.table_name

    def list_to_sql(self, value_list):
        return ','.join([f'({",".join([str(v) for v in _v])})' for _v in value_list])

    def get_sql(self, quote_char=None, **kwargs):
        return format_alias_sql('(VALUES ' + self.list_to_sql(self.value_list) + ')', self.alias, quote_char)

values = [[fn.Date('2020-01-01'), fn.Date('2020-01-02')], [fn.Date('2020-01-03'), fn.Date('2020-01-04')]]

t = ValueList(values, 'tmp')

q = Query()
q = q.from_(t.as_(f'{t.table_name}(a,b)'))
q = q.select('a', 'b')

q.get_sql(quote_char=None)

# SELECT tmp.a,tmp.b FROM (VALUES (DATE('2020-01-01'),DATE('2020-01-02')),(DATE('2020-01-03'),DATE('2020-01-04'))) tmp(a,b)

[danifus]

See also:

• A PR I made for MSSQL with a bit of background for other dbs in the PR text: Add Values class for '(VALUES ...) as alias(field_name, ...)' expressions #517
• Updating records from values #464
• Postgresql Multi Update Conditional !  #524

[anhqle]

This would be great PR to add! Are we waiting for the library owner to respond?

[blazing-gig]

Commenting to know more about the latest status of this ticket and the linked PR. Thanks!

[AzisK]

I am one of the maintainers of the repo now but I need to familiarize with the code myself a bit to provide a better insight. Apart from that, feel free to create such a pull request. Does this pull request #517 cover the requested functionality in this issue? If not, it would be great to have 1 pull request that covers a most of the popular databases