Functions returning TABLE types do not work

[mitchellh]

Version

1.11.0

What happened?

When a function returns a TABLE type, you get an "empty list" error or something like that. I'm sorry I saw this earlier today and don't have the exact output, but the repro below should work.

sqlc generate fails due to an error.

Relevant log output

No response

Database schema

CREATE TABLE accounts (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMAR KEY,
  username   TEXT NOT NULL UNIQUE,
  password   TEXT NOT NULL
);

-- this is a useless and horrifying function cause we don't hash
-- the password, this is just to repro the bug in sqlc
CREATE OR REPLACE FUNCTION register_account(
    _username TEXT,
    _password VARCHAR(70)
)
RETURNS TABLE (
    account_id   INTEGER
)
AS $$
BEGIN
  INSERT INTO accounts (username, password)
       VALUES (
         _username,
         _password
       )
    RETURNING id INTO account_id;

  RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

SQL queries

-- name: Foo :one
SELECT * FROM register_account('a', 'b');

Configuration

No response

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[mitchellh]

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.

[mitchellh]

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 })

[kyleconroy]

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

[Esensats]

"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