RETURNS SETOF <table name> should return the correct DB model instead of []interface{}

[breml]

While working on a solution for #364 which is based on SQL functions, I hit the following problem.

The Go methods generated from calls to SQL functions, that return SETOF, should return the correct DB model.

Same example as in #940.

Given the following definition for PostgreSQL:

CREATE TABLE foo (
            id     INTEGER,
            bar    varchar(100)
);

INSERT INTO foo VALUES (null, 'foo'), (1, 'bar');

CREATE OR REPLACE FUNCTION select1(_id INTEGER)
  RETURNS SETOF foo as
$func$
BEGIN
  IF _id IS NULL THEN
    RETURN QUERY EXECUTE 'select * from foo where id IS NULL';
  ELSE
    RETURN QUERY EXECUTE FORMAT('select * from foo where id = %L', _id);
  END IF;
END
$func$ LANGUAGE plpgsql CALLED ON NULL INPUT;

and the query definition:

-- name: GetSelect1 :many
SELECT select1($1);

the following Go method signature is generated:

func (q *Queries) GetSelect1(ctx context.Context, ID int32) ([]interface{}, error) {

This Go method does return []interface{} instead of []Foo from models.go.

Update: fix typo

[breml]

With to small extensions, it is possible to let sqlc generate the correct return value for this (see: breml@f2ffd6a). The resulting code does not (yet) work though, because the postgresql driver does not manage to scan the result into the given struct. I assume, that there is a problem in the way the return value of type record is handled by the driver. The error message is:

2021/03/18 07:28:37 sql: Scan error on column index 0, name "select1": unsupported Scan, storing driver.Value type string into type *db.Foo2

The current result is always of type string, where the fields are enclosed in brackets like this: (1, bar).

[kyleconroy]

By using the SELECT select1($1) form, you're returning a row type. Composite types aren't supported in lib/pq, but are in pgx. That said, sqlc doesn't support them yet. We're tracking that here #2760.

That said, if you select from the function instead, sqlc generates the correct code.

-- name: GetSelect1 :many
SELECT * FROM select1($1);

https://play.sqlc.dev/p/bc5f900f6b4e5a891dbc7b47b3175c22f5e8eb6003d83ba0ab78bc7aba331c96