Question

我想能够改变名单的位置。每个清单都有自己的立场。在我更新名单时,我想把这一资料留给数据库。因此,我通过一个阵列,在正确的次序中填好清单,并利用一个中度复杂的问题来更新清单:

with pos as (
    select 
        id as pos_id,
        row_number() over() as position
    from (
        select unnest(
            array[36, 33, 35, 37, 46, 39, 40, 41, 43, 45, 38, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 58, 59, 60, 61, 62, 65, 66, 67, 68, 78, 79, 82, 83]
        ) as id 
    ) a 
)  
update lists l
    set position = pos.position
from 
    pos
where 
    l.id = pos.pos_id

这完全在pg_admin中,但当我试图在Node将这项工作提上去时,它刚刚赢得了工作。

I ve tried to send $1 parameter using these forms:

unnest($1)
unnest($1::bigint[])
unnest($1:list)
unnest(ARRAY[$1])
unnest(ARRAY[$1::bigint[])
unnest(ARRAY[$1::list)
unnest(ARRAY[$1::bigint[])
unnest(ARRAY[ + positions.join( , ) + ])
unnest( { + positions.join( , ) + } )
unnest( { + positions.join( , ) + } ::bigint[])

无。其中有些人回去了一种 par差错,有些是“必须添加明确的类型”。我如何通过一个阵列来汇集。本案中的问题?

Answer 1

I should have made some mistake when I tried. Because option 2, unnest($1::bigint) worked fine as I was trying to confirm all forms one by one.

So the correct code is:

let positions = [36, 33, 35, 37, 46, 39, 40, 41, 43, 45, 38, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 58, 59, 60, 61, 62, 65, 66, 67, 68, 78, 79, 82, 83];

pool.query( with pos as ( select id as pos_id, row_number() over() as position from ( select unnest($1::bigint[]) as id ) a )  update listas l set position = pos.position from pos where l.id = pos.pos_id ,
    [positions], (error) => {
... whatever

友情链接