Functions returning TABLE types do not work #1322

Activity

1. mitchellh

   added

   bugSomething isn't workingSomething isn't working

   triageNew issues that hasn't been reviewedNew issues that hasn't been reviewed

   on Dec 1, 2021
2. mitchellh commented on Dec 1, 2021

   

   mitchellh

   on Dec 1, 2021

   Author

   More actions

   Okay, some helpful progress, dumping the catalog.

   The return type for this turns into pg_catalog.record, and the arguments have multiple parameters for each col type of mode == Table. I'm super new to this codebase, but my guess is you need to build a model for each possible function TABLE return type and then resolve to that.
3. mitchellh commented on Dec 1, 2021

   

   mitchellh

   on Dec 1, 2021

   Author

   More actions

   Here's a rough diff I have so far that I think will generate model structs for function returns (lots of opinions in here I didn't know the answer to), but maybe helpful to get you on your way:

   diff --git a/internal/codegen/golang/result.go b/internal/codegen/golang/result.go
   index b213826..b0b9a52 100644
   --- a/internal/codegen/golang/result.go
   +++ b/internal/codegen/golang/result.go
   @@ -10,6 +10,7 @@ import (
           "github.com/kyleconroy/sqlc/internal/config"
           "github.com/kyleconroy/sqlc/internal/core"
           "github.com/kyleconroy/sqlc/internal/inflection"
   + "github.com/kyleconroy/sqlc/internal/sql/ast"
           "github.com/kyleconroy/sqlc/internal/sql/catalog"
    )
    
   @@ -95,6 +96,61 @@ func buildStructs(r *compiler.Result, settings config.CombinedSettings) []Struct
                           }
                           structs = append(structs, s)
                   }
   +
   +         // Functions that return TABLE types need to have structs created.
   +         for _, f := range schema.Funcs {
   +                 var cols []*catalog.Argument
   +                 for _, arg := range f.Args {
   +                         if arg.Mode == ast.FuncParamTable {
   +                                 cols = append(cols, arg)
   +                         }
   +                 }
   +
   +                 // If empty, this isn't a table type return argument.
   +                 if len(cols) == 0 {
   +                         continue
   +                 }
   +
   +                 var tableName string
   +                 if schema.Name == r.Catalog.DefaultSchema {
   +                         tableName = f.Name
   +                 } else {
   +                         tableName = schema.Name + "_" + f.Name
   +                 }
   +                 structName := tableName
   +                 if !settings.Go.EmitExactTableNames {
   +                         structName = inflection.Singular(structName)
   +                 }
   +                 s := Struct{
   +                         Table:   core.FQN{Schema: schema.Name, Rel: f.Name},
   +                         Name:    StructName(structName, settings),
   +                         Comment: f.Comment,
   +                 }
   +                 for _, column := range cols {
   +                         tags := map[string]string{}
   +                         if settings.Go.EmitDBTags {
   +                                 tags["db:"] = column.Name
   +                         }
   +                         if settings.Go.EmitJSONTags {
   +                                 tags["json:"] = JSONTagName(column.Name, settings)
   +                         }
   +                         s.Fields = append(s.Fields, Field{
   +                                 Name: StructName(column.Name, settings),
   +                                 Type: goType(r, compiler.ConvertColumn(&ast.TableName{
   +                                         Catalog: column.Type.Catalog,
   +                                         Schema:  column.Type.Schema,
   +                                         Name:    column.Type.Name,
   +                                 }, &catalog.Column{
   +                                         Name:      column.Name,
   +                                         Type:      *column.Type,
   +                                         IsNotNull: false, // func args are always nullable
   +                                         IsArray:   false, // parser can't currently detect this?
   +                                 }), settings),
   +                                 Tags: tags,
   +                         })
   +                 }
   +                 structs = append(structs, s)
   +         }
           }
           if len(structs) > 0 {
                   sort.Slice(structs, func(i, j int) bool { return structs[i].Name < structs[j].Name })
4. kyleconroy

   added

   enhancementNew feature or requestNew feature or request

   go

   📚 postgresql

   and removed

   triageNew issues that hasn't been reviewedNew issues that hasn't been reviewed

   on Dec 1, 2021
5. kyleconroy

   added

   🔧 golang

   and removed

   go

   on Jan 26, 2022
6. kyleconroy

   added

   analyzer

   on Sep 22, 2023
7. kyleconroy

   added 2 commits that reference this issue on Oct 13, 2023

   Fix #1322

   085ce34

   Fix #1322

   3c14aba
8. kyleconroy

   closed this as completedin 0cfadb0on Oct 16, 2023
9. kyleconroy commented on Oct 24, 2023

   

   kyleconroy

   on Oct 24, 2023

   Collaborator

   More actions

   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
10. Esensats commented on Apr 9, 2025

    

    Esensats

    on Apr 9, 2025

    Last edited by Esensats

    More actions

    "Anonymous" tables with multiple fields still return interface{}: playground

    Just changing it to:

    CREATE TABLE temp_table (f1 INTEGER, f2 TEXT); -- 1. Add temporary table
    
    CREATE FUNCTION register_account()
    RETURNS temp_table -- 2. Use it as return type
    AS $$
    BEGIN
      RETURN (5, "lol");
    END;
    $$ LANGUAGE plpgsql;
    
    DROP TABLE temp_table; -- 3. Optionally drop it

    works but it's such a crutch and I don't understand why can't the "anonymous" table just be interpreted as a table with the name equal to the name of the function or something like that. Is the problem in potential naming conflicts? Is there a relevant issue? I can't find one

    ---

    Edit: found a less crutchy solution

    👍React with 👍1Reacted by EmpathyZeroed
11. alfonsodev

    added a commit that references this issue on Oct 13, 2025

    test(analyzer): Add more database analyzer test cases (sqlc-dev#2854)

    ...

    dbaa567