is unnest supported?

[tcastelly]

Hello,

Is allowed to use unnest?

INSERT INTO inventory(id, json_row, name)
SELECT *
FROM unnest(Array[48]::int[], ARRAY['{}']::json[], ARRAY['me']::varchar[]);

I have this error:

function unnest(unknown, unknown) does not exist

It's maybe a duplicate issue for: https://github.com/kyleconroy/sqlc/issues/670

But the error message is not the same anymore.

Thank you very much.

[maratori]

I successfully use following query with unnest

-- name: InsertOutdatedEntity :exec
INSERT INTO outdated_entities (entity_type, entity_id, outdated_at)
SELECT @entity_type, unnest(@ids::UUID[]), @outdated_at
ON CONFLICT (entity_type, entity_id) DO UPDATE
    SET outdated_at = GREATEST(outdated_entities.outdated_at, EXCLUDED.outdated_at);

[tcastelly]

Thank you for your answer.

I can compile your query. In fact I can compile this:

SELECT unnest(ARRAY[48]::int[])

But because I need multiple arrays in unnest it looks like I have to use:

SELECT * FROM unnest(ARRAY[48]::int[], ARRAY['{}']::json[]);

and here it's broken:

function unnest(unknown, unknown) does not exist

[maratori]

Have you tried multiple unnests for multiple arrays? Something like this should work:

-- name: InsertOutdatedEntity :exec
INSERT INTO outdated_entities (entity_type, entity_id, outdated_at)
SELECT unnest(@entity_types::VARCHAR[]), unnest(@ids::UUID[]), @outdated_at;

[tcastelly]

Oh yes thank you :)

[tcastelly]

I'm sorry to re-open this old issue. But it seems than the "unnest" example from the Postgres documentation is not working with sqlc. I'm wondering if it's something can be fixed or no.

https://www.postgresql.org/docs/current/functions-array.html

select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b);

1,foo
2,bar
<null>,baz

result: https://play.sqlc.dev/p/29816032c2fa5f604524ad3089121ef2284bf59c84d871ede995f78bcd4f11c1

Split unsset can be build by sqlc but it's not a solution because the result of the query is not the same:

select * from unnest(ARRAY[1,2]) as a, unnest(ARRAY['foo','bar','baz']) as b;

1,foo
1,bar
1,baz
2,foo
2,bar
2,baz

Thank you very much.

[earthboundkid]

I have had this same issue. My workaround was to use JSONB instead of a pair of ARRAYs, but it's ugly.

[tcastelly]

I have had this same issue. My workaround was to use JSONB instead of a pair of ARRAYs, but it's ugly.

Hi @carlmjohnson do you have an example? Because I've tried something like this but it's not working

WITH to_update as (
    SELECT x
    FROM JSON_TO_RECORDSET(@auth_users::JSON)
             AS x("id" BIGINT, "label" VARCHAR(55))
)
UPDATE auth_users
SET label = to_update.label
FROM to_update
WHERE auth_users.id = to_update.id
  AND auth_users.user_id = @user_id;

Issue: https://github.com/kyleconroy/sqlc/issues/2191

Thank you :)

[earthboundkid]

https://github.com/spotlightpa/almanack/blob/d48b9e2167754e56b729788fe04d1270f00c38af/sql/queries/gdocs_image.sql#L6

[kyleconroy]

Going to close this out as we have a pretty good workaround.