Calculation referencing a resource in a different schema

I'm attempting to do a ST_Union on a related resource, but that resource is stored in a different schema

calculate :geom, :geometry, expr(fragment("ST_UNION(?)", lgas.geog))

calculate :geom, :geometry, expr(fragment("ST_UNION(?)", lgas.geog))

It seems that Ash is not adding the schema (which is defined on the resource) to the query i.e:

* ** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "public.lga" does not exist

    query: SELECT DISTINCT ON (s0."id") s0."id", s0."name", s0."inserted_at", s0."updated_at", s0."area", (ST_UNION(l2."geog"))::geometry FROM "service_areas" AS s0 LEFT OUTER JOIN "public"."lga_service_areas" AS l1 ON s0."id" = l1."service_area_id" LEFT OUTER JOIN "public"."lga" AS l2 ON l2."gid" = l1."gid" WHERE (s0."id"::uuid = $1::uuid::uuid)

* ** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "public.lga" does not exist

    query: SELECT DISTINCT ON (s0."id") s0."id", s0."name", s0."inserted_at", s0."updated_at", s0."area", (ST_UNION(l2."geog"))::geometry FROM "service_areas" AS s0 LEFT OUTER JOIN "public"."lga_service_areas" AS l1 ON s0."id" = l1."service_area_id" LEFT OUTER JOIN "public"."lga" AS l2 ON l2."gid" = l1."gid" WHERE (s0."id"::uuid = $1::uuid::uuid)

Is there a way to define the schema that I'm missing, or is this a bug?

5 Replies

ZachDaniel•5mo ago

Sounds like a bug Is there multitenancy at play here

kernelOP•5mo ago

nope just the gis datasets sitting in a 'gis' schema - because they are created by some weird shp2pgsql tool and I'd like to keep them separate

ZachDaniel•5mo ago

Yeah then it's a bug. Please open an issue I'll look in the morning

Rebecca Le•5mo ago

is there a table name wrong somewhere? lgas vs lga

kernelOP•5mo ago

nope lga is just the council polygon dataset from Australian gov @Rebecca Le still ridiculous that you can't get postcode data without paying Auspost an arm and a leg I'll try get a repro in that commit you made Zach https://github.com/ash-project/ash_postgres/pull/563

Gaming

Programming

Calculation referencing a resource in a different schema

Did you find this page helpful?