pgx版权与何地条件

Question

我想使用pgx (通过sqlc)具有从哪里抄送条件的copyfrom特性：

(这一条件取自关于插入的工作规则)：

    WHERE (EXISTS ( SELECT *
                FROM asns
                WHERE merchantId IS NOT DISTINCT FROM NEW.merchantId
                AND return_provider IS NOT DISTINCT FROM NEW.return_provider
                AND barcode IS NOT DISTINCT FROM NEW.barcode
                AND carrier IS NOT DISTINCT FROM NEW.carrier
                AND tracking_number IS NOT DISTINCT FROM NEW.tracking_number
                AND customer_email IS NOT DISTINCT FROM NEW.customer_email
                AND order_id IS NOT DISTINCT FROM NEW.order_id
                AND order_name IS NOT DISTINCT FROM NEW.order_name
                AND order_number IS NOT DISTINCT FROM NEW.order_number
                AND return_line_item_id IS NOT DISTINCT FROM NEW.return_line_item_id
                AND rma IS NOT DISTINCT FROM NEW.rma
                AND sku IS NOT DISTINCT FROM NEW.sku)) DO INSTEAD NOTHING;

在pgx中有办法做到这一点吗？谢谢。

Answer 1

是的，这是有办法的。一个人可以用

func (pgConn *PgConn) CopyFrom(ctx context.Context, r io.Reader, sql string) (CommandTag, error)。

您可以在测试用例中找到使用示例：

func TestConnCopyFrom(t *testing.T) {
    t.Parallel()

    pgConn, err := pgconn.Connect(context.Background(), os.Getenv("PGX_TEST_DATABASE"))
    require.NoError(t, err)
    defer closeConn(t, pgConn)

    if pgConn.ParameterStatus("crdb_version") != "" {
        t.Skip("Server does not fully support COPY FROM (https://www.cockroachlabs.com/docs/v20.2/copy-from.html)")
    }

    _, err = pgConn.Exec(context.Background(), `create temporary table foo(
        a int4,
        b varchar
    )`).ReadAll()
    require.NoError(t, err)

    srcBuf := &bytes.Buffer{}

    inputRows := [][][]byte{}
    for i := 0; i < 1000; i++ {
        a := strconv.Itoa(i)
        b := "foo " + a + " bar"
        inputRows = append(inputRows, [][]byte{[]byte(a), []byte(b)})
        _, err = srcBuf.Write([]byte(fmt.Sprintf("%s,\"%s\"\n", a, b)))
        require.NoError(t, err)
    }

    ct, err := pgConn.CopyFrom(context.Background(), srcBuf, "COPY foo FROM STDIN WITH (FORMAT csv)")
    require.NoError(t, err)
    assert.Equal(t, int64(len(inputRows)), ct.RowsAffected())

    result := pgConn.ExecParams(context.Background(), "select * from foo", nil, nil, nil, nil).Read()
    require.NoError(t, result.Err)

    assert.Equal(t, inputRows, result.Rows)

    ensureConnValid(t, pgConn)
}

只需用COPY foo FROM STDIN ...语句扩展WHERE。