CODEWITHSUNDEEP
postgresql
Adam Benson
Adam Benson

Reputation: 8542

Lateral query syntax

I'm trying to get lateral to work in a Postgres 9.5.3 query.

select  b_ci."IdOwner",
ci."MinimumPlaces",
ci."MaximumPlaces",
(select count(*) from "LNK_Stu_CI" lnk
where lnk."FK_CourseInstanceId" = b_ci."Id") as "EnrolledStudents",

from "Course" c
join "DBObjectBases" b_c on c."Id" = b_c."Id"

join "DBObjectBases" b_ci on b_ci."IdOwner" = b_c."Id"
join "CourseInstance" ci on ci."Id" = b_ci."Id",

lateral (select ci."MaximumPlaces" - "EnrolledStudents") x

I want the right-most column to be the result of "MaximumPlaces" - "EnrolledStudents" for that row but am struggling to get it to work. At the moment PG is complaining that "EnrolledStudents" does not exist - which is exactly the point of "lateral", isn't it?

select  b_ci."IdOwner",
ci."MinimumPlaces",
ci."MaximumPlaces",
(select count(*) from "LNK_Stu_CI" lnk
where lnk."FK_CourseInstanceId" = b_ci."Id") as "EnrolledStudents",
lateral (select "MaximumPlaces" - "EnrolledStudents") as "x"

from "Course" c
join "DBObjectBases" b_c on c."Id" = b_c."Id"

join "DBObjectBases" b_ci on b_ci."IdOwner" = b_c."Id"
join "CourseInstance" ci on ci."Id" = b_ci."Id"

If I try inlining the lateral clause (shown above) in the select it gets upset too and gives me a syntax error - so where does it go?

Thanks,

Adam.

Upvotes: 1

Views: 762

Answers (1)

MatheusOl
MatheusOl

Reputation: 11835

You are missing the point with LATERAL. It can access columns in tables in the FROM clause, but not aliases defined in SELECT clause.

If you want to access alias defined in SELECT clause, you need to add another query level, either using a subquery in FROM clause (AKA derived table) or using a CTE (Common Table Expression). As CTE in PostgreSQL acts as an optimization fence, I strongly recommend going with subquery in this case, like:

select
    -- get all columns on the inner query
    t.*,
    -- get your new expression based on the ones defined in the inner query
    t."MaximumPlaces" - t."EnrolledStudents" AS new_alias
from (
    select  b_ci."IdOwner",
    ci."MinimumPlaces",
    ci."MaximumPlaces",
    (select count(*) from "LNK_Stu_CI" lnk
    where lnk."FK_CourseInstanceId" = b_ci."Id") as "EnrolledStudents",

    from "Course" c
    join "DBObjectBases" b_c on c."Id" = b_c."Id"

    join "DBObjectBases" b_ci on b_ci."IdOwner" = b_c."Id"
    join "CourseInstance" ci on ci."Id" = b_ci."Id"
) t

Upvotes: 3

Related Questions