Unable to use set returning functions in from clause with ordinality

Version

1.21.0

What happened?

column does not exist

Relevant log output

column "item_object" does not exist

Database schema

No response

SQL queries

SELECT code,arr.item_object->> 'code'  as resource_code
FROM sys_actions,
    jsonb_array_elements(resources) with ordinality arr(item_object, resource)
    WHERE item_object->>'resource' = sqlc.arg('resource')
    LIMIT 1;

Configuration

sql:
  - schema: "./sql/schema/"
    queries: "./sql/query/"
    engine: "postgresql"
    gen:
      go:
        package: "queries"
        out: "internal/data/queries"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_empty_slices: false
        emit_result_struct_pointers: true
        emit_exported_queries: true
        emit_methods_with_db_argument: false
        emit_pointers_for_null_types: true

Playground URL

https://play.sqlc.dev/p/4b891b0542344db335f87e02995f28a3d2d98a0b6821747938ceecf73fc38a46

What operating system are you using?

No response

What database engines are you using?

No response

What type of code are you generating?

No response

SELECT sa.code as menu_code,record.code as resource_code
FROM sys_actions as sa,jsonb_to_recordset(sa.resources) AS record(code VARCHAR,resource VARCHAR)
WHERE record.resource = $1 LIMIT 1;

https://play.sqlc.dev/p/029d972cfbb5084f1da6c88c9c30f963cb0e1b24c4c6eaf8b76f8e909fef69d0

I found the same error with the next query, trying to make queries in jsonb fields:

SELECT id, y.x->>'lang' AS lang, y.x->>'name' AS gender
FROM genders g, 
LATERAL (SELECT jsonb_array_elements(g.gender) AS x) AS y
WHERE g.id = $1 AND y.x->>'lang' = $2  LIMIT 1;

Table alias "y" does not exist, hope to help...

Similar to #1322

test: Add case for #2731

test: Add more test cases for new analyzer (#2879)

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground

test: Add more test cases for new analyzer (sqlc-dev#2879)

Navigation Menu

Repository navigation

Unable to use set returning functions in from clause with ordinality #2731

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Unable to use set returning functions in from clause with ordinality #2731

Description

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Activity

Lay523 commented on Sep 14, 2023

arturo-villalpando commented on Sep 19, 2023

kyleconroy commented on Oct 2, 2023

kyleconroy commented on Oct 24, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions