Problem with selecting from functions
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
I also have a similar problem, any update?
I also have a similar problem, any update?
No, I rewrite method by myself in file.
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
Try to cast types in select
select title::varchar(100), image::varchar(100), organization_name::varchar(100), organization_logo::varchar(100)
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
any news? seems it is not possible select certain columns from function
@ruslan-polutsygan ,casting work, but of course its not pleasant to write again their types
@Rehart-Kcalb missed that. thanks for pointing out
Had the same issue and casting every column worked. Seems a little weird.