Ud history custom sql request crashes
After #37 (closed), the UD amount is now BigInt
instead of number
, and I get crash of the custom SQL function
database logs
db-1 | 2024-10-28 14:09:19.743 UTC [2067474] ERROR: structure of query does not match function result type
db-1 | 2024-10-28 14:09:19.743 UTC [2067474] DETAIL: Returned type numeric does not match expected type integer in column 2.
db-1 | 2024-10-28 14:09:19.743 UTC [2067474] CONTEXT: SQL statement "SELECT
db-1 | CAST('ud-' || identity_row.name || '-' || ud.block_number || '-' || ud.event_id AS character varying) AS id,
db-1 | ud.amount,
db-1 | ud.block_number,
db-1 | b.timestamp,
db-1 | identity_row.id AS identity_id
db-1 | FROM
db-1 | public.universal_dividend ud
db-1 | JOIN public.block b ON ud.block_number = b.height
db-1 | WHERE
db-1 | EXISTS (
db-1 | SELECT 1
db-1 | FROM (
db-1 | SELECT
db-1 | me1.block_number as creation_block,
db-1 | COALESCE(
db-1 | (
db-1 | SELECT me2.block_number
db-1 | FROM public.membership_event me2
db-1 | WHERE me2.identity_id = me1.identity_id
db-1 | AND me2.event_type = 'Removal'
db-1 | AND me2.block_number > me1.block_number
db-1 | ORDER BY me2.block_number
db-1 | LIMIT 1
db-1 | ),
db-1 | (SELECT MAX(block_number) FROM public.universal_dividend)
db-1 | ) as removal_block
db-1 | FROM public.membership_event me1
db-1 | WHERE me1.identity_id = identity_row.id
db-1 | AND me1.event_type = 'Creation'
db-1 | ) as membership_periods
db-1 | WHERE ud.block_number >= membership_periods.creation_block
db-1 | AND ud.block_number <= membership_periods.removal_block
db-1 | )"
db-1 | PL/pgSQL function get_ud_history(identity) line 3 at RETURN QUERY
db-1 | 2024-10-28 14:09:19.743 UTC [2067474] STATEMENT: SELECT coalesce((json_agg("root" )->0), 'null' ) AS "root" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.base"."id" AS "id", "_root.ar.root.transfersIssued"."transfersIssued" AS "transfersIssued", "_root.ar.root.transfersReceived"."transfersReceived" AS "transfersReceived", "_root.or.identity"."identity" AS "identity", "_root.or.linkedIdentity"."linkedIdentity" AS "linkedIdentity", "_root.ar.root.wasIdentity"."wasIdentity" AS "wasIdentity", 'Account' AS "__typename" ) AS "_e" ) ) AS "root" FROM (SELECT * FROM "public"."account" WHERE (("public"."account"."id") = (($5)::varchar)) ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.or.identity.base"."id" AS "id", "_root.or.identity.base"."name" AS "name", upper("_root.or.identity.base"."status" ) AS "status", "_root.or.identity.cf.udHistory"."udHistory" AS "udHistory", 'Identity' AS "__typename" ) AS "_e" ) ) AS "identity" FROM (SELECT * FROM "public"."identity" WHERE (("_root.base"."id") = ("account_id")) LIMIT 1 ) AS "_root.or.identity.base" LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg("udHistory" ORDER BY "root.or.identity.cf.udHistory.pg.block_number" DESC NULLS FIRST), '[]' ) AS "udHistory" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.or.identity.cf.udHistory.base"."id" AS "id", "_root.or.identity.cf.udHistory.base"."timestamp" AS "timestamp", "_root.or.identity.cf.udHistory.base"."amount" AS "amount", "_root.or.identity.cf.udHistory.base"."block_number" AS "blockNumber", 'UdHistory' AS "__typename" ) AS "_e" ) ) AS "udHistory", "_root.or.identity.cf.udHistory.base"."block_number" AS "root.or.identity.cf.udHistory.pg.block_number" FROM (SELECT * FROM "public"."get_ud_history"("_root.or.identity.base") AS "_get_ud_history" WHERE (("_get_ud_history"."block_number") > (($4)::integer)) ORDER BY "block_number" DESC NULLS FIRST LIMIT 20 ) AS "_root.or.identity.cf.udHistory.base" ORDER BY "root.or.identity.cf.udHistory.pg.block_number" DESC NULLS FIRST ) AS "_root.or.identity.cf.udHistory" ) AS "_root.or.identity.cf.udHistory" ON ('true') ) AS "_root.or.identity" ON ('true') LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.or.linkedIdentity.base"."id" AS "id", "_root.or.linkedIdentity.base"."name" AS "name", 'Identity' AS "__typename" ) AS "_e" ) ) AS "linkedIdentity" FROM (SELECT * FROM "public"."identity" WHERE (("_root.base"."linked_identity_id") = ("id")) LIMIT 1 ) AS "_root.or.linkedIdentity.base" ) AS "_root.or.linkedIdentity" ON ('true') LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg("transfersReceived" ORDER BY "root.ar.root.transfersReceived.pg.block_number" DESC NULLS FIRST), '[]' ) AS "transfersReceived" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.transfersReceived.base"."id" AS "id", "_root.ar.root.transfersReceived.base"."from_id" AS "fromId", "_root.ar.root.transfersReceived.base"."amount" AS "amount", "_root.ar.root.transfersReceived.base"."timestamp" AS "timestamp", "_root.ar.root.transfersReceived.base"."block_number" AS "blockNumber", "_root.ar.root.transfersReceived.or.comment"."comment" AS "comment", 'Transfer' AS "__typename" ) AS "_e" ) ) AS "transfersReceived", "_root.ar.root.transfersReceived.base"."block_number" AS "root.ar.root.transfersReceived.pg.block_number" FROM (SELECT * FROM "public"."transfer" WHERE ((("_root.base"."id") = ("to_id")) AND (("public"."transfer"."block_number") > (($3)::integer))) ORDER BY "block_number" DESC NULLS FIRST LIMIT 10 ) AS "_root.ar.root.transfersReceived.base" LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.transfersReceived.or.comment.base"."id" AS "id", "_root.ar.root.transfersReceived.or.comment.base"."remark" AS "remark", upper("_root.ar.root.transfersReceived.or.comment.base"."type" ) AS "type", 'TxComment' AS "__typename" ) AS "_e" ) ) AS "comment" FROM (SELECT * FROM "public"."tx_comment" WHERE (("_root.ar.root.transfersReceived.base"."comment_id") = ("id")) LIMIT 1 ) AS "_root.ar.root.transfersReceived.or.comment.base" ) AS "_root.ar.root.transfersReceived.or.comment" ON ('true') ORDER BY "root.ar.root.transfersReceived.pg.block_number" DESC NULLS FIRST ) AS "_root.ar.root.transfersReceived" ) AS "_root.ar.root.transfersReceived" ON ('true') LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg("transfersIssued" ORDER BY "root.ar.root.transfersIssued.pg.block_number" DESC NULLS FIRST), '[]' ) AS "transfersIssued" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.transfersIssued.base"."id" AS "id", "_root.ar.root.transfersIssued.base"."to_id" AS "toId", "_root.ar.root.transfersIssued.base"."amount" AS "amount", "_root.ar.root.transfersIssued.base"."timestamp" AS "timestamp", "_root.ar.root.transfersIssued.base"."block_number" AS "blockNumber", "_root.ar.root.transfersIssued.or.comment"."comment" AS "comment", 'Transfer' AS "__typename" ) AS "_e" ) ) AS "transfersIssued", "_root.ar.root.transfersIssued.base"."block_number" AS "root.ar.root.transfersIssued.pg.block_number" FROM (SELECT * FROM "public"."transfer" WHERE ((("_root.base"."id") = ("from_id")) AND (("public"."transfer"."block_number") > (($2)::integer))) ORDER BY "block_number" DESC NULLS FIRST LIMIT 10 ) AS "_root.ar.root.transfersIssued.base" LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.transfersIssued.or.comment.base"."id" AS "id", "_root.ar.root.transfersIssued.or.comment.base"."remark" AS "remark", upper("_root.ar.root.transfersIssued.or.comment.base"."type" ) AS "type", 'TxComment' AS "__typename" ) AS "_e" ) ) AS "comment" FROM (SELECT * FROM "public"."tx_comment" WHERE (("_root.ar.root.transfersIssued.base"."comment_id") = ("id")) LIMIT 1 ) AS "_root.ar.root.transfersIssued.or.comment.base" ) AS "_root.ar.root.transfersIssued.or.comment" ON ('true') ORDER BY "root.ar.root.transfersIssued.pg.block_number" DESC NULLS FIRST ) AS "_root.ar.root.transfersIssued" ) AS "_root.ar.root.transfersIssued" ON ('true') LEFT OUTER JOIN LATERAL (SELECT coalesce(json_agg("wasIdentity" ), '[]' ) AS "wasIdentity" FROM (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.wasIdentity.base"."id" AS "id", "_root.ar.root.wasIdentity.base"."next_id" AS "nextId", "_root.ar.root.wasIdentity.or.identity"."identity" AS "identity", 'ChangeOwnerKey' AS "__typename" ) AS "_e" ) ) AS "wasIdentity" FROM (SELECT * FROM "public"."change_owner_key" WHERE (("_root.base"."id") = ("previous_id")) ) AS "_root.ar.root.wasIdentity.base" LEFT OUTER JOIN LATERAL (SELECT row_to_json((SELECT "_e" FROM (SELECT "_root.ar.root.wasIdentity.or.identity.base"."name" AS "name", 'Identity' AS "__typename" ) AS "_e" ) ) AS "identity" FROM (SELECT * FROM "public"."identity" WHERE (("_root.ar.root.wasIdentity.base"."identity_id") = ("id")) LIMIT 1 ) AS "_root.ar.root.wasIdentity.or.identity.base" ) AS "_root.ar.root.wasIdentity.or.identity" ON ('true') ) AS "_root.ar.root.wasIdentity" ) AS "_root.ar.root.wasIdentity" ON ('true') ) AS "_root"
@pokapow, do you have time to fix it?
Edited by Hugo Trentesaux