Problem with selecting from functions

[Rehart-Kcalb]

Version

1.26.0

What happened?

I have query

select title,image,organization_name,organization_logo from filter($1,$2::bigint[],$3,$4);

when I generate code sqlc output such message

column "title" does not exist

When I have tried to return without title, it just return

column column_name does not exist

It didn't show any error only when I write this

select * from filter($1,$2::bigint[],$3,$4);

But after it generate code which return {}interface slice, and I have problem with serializing it. So I rewrite this method by myself everytime when I change my query file.

Relevant log output

# package
sql/query.sql:111:8: column "title" does not exist

Database schema

CREATE OR REPLACE FUNCTION filter(
    title_param text, 
    category_ids bigint[], 
    "limit_param" int, 
    "offset_param" int
)
RETURNS TABLE (
    title varchar(100),
    organization_name varchar(100),
    organization_logo varchar(100),
    image varchar(100)
) AS $$
BEGIN
    IF title_param = '' AND array_length(category_ids, 1) IS NULL THEN
        -- Case where both title_param and category_ids are empty
        RETURN QUERY
        SELECT
            courses.title,
            users.firstname AS organization_name,
            users.profile as organization_logo,
            courses.image
        FROM
            courses
            LEFT JOIN users ON users.id = courses.course_provider
        WHERE
            users.id = courses.course_provider
        LIMIT limit_param
        OFFSET offset_param;
    ELSE IF title_param <> '' AND array_length(category_ids, 1) IS NULL THEN
        -- Case where title_param is not empty, but category_ids is empty
        RETURN QUERY
        SELECT
            courses.title,
            users.firstname AS organization_name,
            users.profile as organization_logo,
            courses.image
        FROM
            courses
            LEFT JOIN users ON users.id = courses.course_provider
        WHERE
            courses.title ILIKE '%' || title_param || '%'
        LIMIT limit_param
        OFFSET offset_param;
    ELSE IF title_param = '' AND array_length(category_ids, 1) IS NOT NULL THEN
        -- Case where title_param is empty, but category_ids is not empty
        RETURN QUERY
        SELECT
            Distinct(courses.title),
            users.firstname AS organization_name,
            users.profile as organization_logo,
            courses.image
        FROM
            courses
            LEFT JOIN users ON users.id = courses.course_provider
            Left join course_categories on courses.id = course_categories.course_id
        WHERE
            course_categories.category_id = ANY(category_ids)
        LIMIT limit_param
        OFFSET offset_param;
    ELSE
        -- Case where both title_param and category_ids are not empty
        RETURN QUERY
        SELECT
            Distinct(courses.title),
            users.firstname AS organization_name,
            users.profile as organization_logo,
            courses.image
        FROM
            courses
            LEFT JOIN users ON users.id = courses.course_provider
            Left join course_categories on courses.id = course_categories.course_id
        WHERE
            courses.title ILIKE '%' || title_param || '%'
            AND course_categories.category_id = ANY(category_ids)
        LIMIT limit_param
        OFFSET offset_param;
    END IF;
    END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;

SQL queries

select title,image,organization_name,organization_logo from filter($1,$2::bigint[],$3,$4);

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "sql/migrations",
    "queries": "sql/query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "/internal/db",
        "emit_json_tags": true,
        "sql_package": "pgx/v5"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/203f6144cf5310949dacf1eabe1cbf7f4f1b65915f86cf5be33c4147aefdf8c9

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[handysuherman]

I also have a similar problem, any update?

[Rehart-Kcalb]

I also have a similar problem, any update?

No, I rewrite method by myself in file.

[Davincible]

Same for me, any select from a function return interface slice, sqlc not able to parse function outputs. @kyleconroy

Any SELECT * FROM custom_function() doesn't seem to work. Went back to 1.22 but never seemed to have worked

[salojoo]

Try to cast types in select

select title::varchar(100), image::varchar(100), organization_name::varchar(100), organization_logo::varchar(100)

[Rehart-Kcalb]

Try to cast types in select

select title::varchar(100), image::varchar(100), organization_name::varchar(100), organization_logo::varchar(100)

I tried this when initially post this but it outputed error message