Support postgresql VALUES lists

[io41]

Example:

UPDATE names SET name = vals.name
FROM (VALUES (1, "Bob"), (2, "Mary"), (3, "Jane")) AS vals (id, name)
WHERE names.id = vals.id;

See https://www.postgresql.org/docs/9.5/queries-values.html

Is there a way to make pugsql generic enough to generate any SQL, regardless of whether or not the specific driver supports it, thus solving this in a more generic way?

[mcfunley]

So hugsql has stuff like this:
https://www.hugsql.org/#param-value-list

This kind of syntax would require pugsql parsing the sql itself, as opposed to leaning entirely on sqlalchemy to do that. I'm definitely down to do this if it becomes necessary.

I've started down the road of implementing that a few times, but every time I've done this I've figured out some way to just let sqlalchemy do the hard part.

Multi-row inserts are very similar to this:
https://pugsql.org/tutorial#multi-row-inserts

And I'm wondering if that approach also works here.

• If it does, then the thing to do would just be to document this.
• If it doesn't then yeah I'd agree it's probably time to bite the bullet and parse the sql.

If there's a way it works but that way is weird/inconsistent with the insert usage of VALUES, then maybe it's time to bite the bullet and parse the sql anyway.

[io41]

And I'm wondering if that approach also works here.

I wondered the same myself, but couldn't see how to do it, without inferring the intended use by the user... and that seemed difficult (impossible?), or at least too magical.

I'm new to pugsql (and never used hugsql), but perhaps parameter types are needed, to make it clear how these should be rendered: https://www.hugsql.org/#param-types

There're other types of collections that may need to be rendered too, such as arrays, {1, 2, 3} or multidimensional arrays, {{1,2,3}, {4,5,6}}, which use brackets rather than parens.

From the PostgreSQL documentation on value delimiters:

Among the standard data types provided in the PostgreSQL distribution, all use a comma (,), except for type box which uses a semicolon (;).

So for collections it seems the delimiter may differ, as does the enclosing symbol. Not sure what various types other DBs use.

Hugsql appears to use the correct type depending on the clojure data type used (untested), but also supports specifying what type a param is. Is this approach possible with pugsql? Would that still allow it to use sqla to do the hard part?

[mcfunley]

Yeah, you are probably right about this. Seems worth parsing the sql, although that’s gonna be a significant addition.

[Yeganloo]

I'am not sure if i understand you correctly but it seems that sqla support what pugsql need here.
take a look at: https://stackoverflow.com/questions/27656459/sqlalchemy-and-raw-sql-list-as-an-input
Sample code:

import sqlalchemy

args = [1, 2, 3]
raw_sql = "SELECT * FROM table WHERE data IN :values" 
query = sqlalchemy.text(raw_sql).bindparams(values=tuple(args))
conn.engine.execute(query)

[mackenney]

Hi, nice library :)

I have extended pugsql with this snippet to make use of many values in simple inserts.

"""
Adds a insert statement to a Module that allows inserting multiple values per query
"""
import pugsql
import sqlalchemy as sa


class FastInsertStatement(pugsql.statement.Statement):
    def __init__(self, name: str, table_name: str, column_names: list[str]):
        columns_str = ",".join(column_names)
        params_str = ",".join([f":{c}" for c in column_names])
        sql = f"INSERT INTO {table_name} ({columns_str}) VALUES ({params_str})"
        insert_stmt = sa.table(
            table_name, *[sa.column(c) for c in column_names]
        ).insert()
        super().__init__(
            name, sql, "this is a fast insert statement", pugsql.parser._insert, None
        )
        self._table_name = table_name
        self._column_names = column_names
        self._slow_text = self._text
        self._text = insert_stmt

    def _param_names(self):
        def kfn(p):
            return self.sql.index(":" + p)

        return sorted(self._slow_text._bindparams.keys(), key=kfn)


def add_fast_insert_statement(
    module: pugsql.compiler.Module,
    statement_name: str,
    table_name: str,
    column_names: list[str],
) -> None:
    stmt = FastInsertStatement(statement_name, table_name, column_names)

    if hasattr(module, statement_name):
        if statement_name not in module._statements:
            raise ValueError(
                f'Error adding FastInsertStatement - the function name "{statement_name}"'
                " is reserved. Please choose another name."
            )
        raise ValueError(
            "Error adding FastInsertStatement - there already exists a Statement with "
            f"the name {statement_name} in {getattr(module, statement_name).filename}"
        )

    stmt.set_module(module)

    setattr(module, statement_name, stmt)
    module._statements[statement_name] = stmt