Unable to use a function in my query

[ccp-norbert]

Hi,

I'm trying to execute the following query: "SELECT * FROM my_function()". I run this using the pgx.conn.Query() method, returning rows I then attempt to scan with pgxscan.ScanAll() and I get the following error: scany: column: 'my_function': no corresponding field found, or it's unexported.

I'm a bit confused as to why this is happening, what am I doing wrong? Thanks.

[georgysavva]

Hello and thanks for your issue! I guess this happens because of the result columns in the SELECT, try to remove "*" and list the columns that you want to be returned explicitly. I think this will help to debug and fix your SQL query.

[ccp-norbert]

Thanks for your answer! This has indeed fixed it, but next, scany is now complaining about the columns themselves. It's however mentioning column names in full lowercase, so I'm wondering if I'm missing some important part here again.

This is what my SQL function returns: RETURNS TABLE (UserID bigint, Name text, Description text, Validated bool) AS

These names don't match the columns in the actual SQL table, but I've made them match the struct I'm feeding ScanAll:

type MyStruct struct {
    UserID      int64
    Name        string
    Description string
    Validated   bool
}

I use PGX to perform the following query: SELECT UserID, Name, Description, Validated FROM my_function($1, $2) And then scany: scanErr := pgxscan.ScanAll(results, rows) with rows being the result from pgx, and results a reference to var output []MyStruct.

I get the following error: scany: column: 'userid': no corresponding field found, or it's unexported in MyStruct.

Can you spot any obvious mistakes? I've been dancing around with this for a while without any progress.

Thanks in advance!

Edit: I have fixed this by adding a "db: "userid" tag in my struct definition, although I still don't understand why this problem was happening, especially since scany hasn't complained about any of the following columns (which don't need a tag to work).

[georgysavva]

Check this docs section: https://pkg.go.dev/github.com/georgysavva/scany@v0.2.8/dbscan#hdr-Mapping_struct_field_to_database_column

By default scany translates struct fields to snake case, so for UserID field it looks for user_id column.

[ccp-norbert]

I see, I had completely missed this section of the documentation! It sounds like the correct approach for me would be to remove my db tags but then rename my SQL function out parameters to snake case to comply with scany's default behaviour.

Thanks a lot for your answers again, I'm closing the issue as I believe I've got all the answers I was looking for.