Support composite types

[kyleconroy]

CREATE TYPE

CREATE TYPE myarg as (
    id integer,
    name text
);

-- name: BasicQuery :one
select $1::myarg;

Automatic table types

https://www.postgresql.org/docs/current/rowtypes.html

Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type. For example, had we said:

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: AutomatiicType :many
select authors from authors;

[kirk-anchor]

Support for composite types of a TABLE/TYPE would be great. A further improvement would be supporting arbitrary record types that don't belong to a TABLE/TYPE if that would be possible.

postgres=# select row(1, 'foo');
   row
---------
 (1,foo)
(1 row)

[Yiwen-Gao]

Following! Our schema defines tables that use custom types, some of which use additional custom types. My expectation was that Go structs would be generated for these types as they are for tables. Is https://github.com/sqlc-dev/sqlc/issues/309#issuecomment-580228943 still the recommended workaround: create Go structs for custom types by hand then use these in overrides? Thank you!

Schema definitions:

CREATE TYPE "TransactionMessageHeader" AS (
    num_required_signatures SMALLINT,
    num_readonly_signed_accounts SMALLINT,
    num_readonly_unsigned_accounts SMALLINT
);

CREATE TYPE "TransactionMessage" AS (
    header "TransactionMessageHeader",
    account_keys BYTEA[],
    recent_blockhash BYTEA,
    instructions "CompiledInstruction"[]
);

-- other custom types ...

CREATE TABLE transaction (
    slot BIGINT NOT NULL,
    signature BYTEA NOT NULL,
    is_vote BOOL NOT NULL,
    message_type SMALLINT, -- 0: legacy, 1: v0 message
    legacy_message "TransactionMessage",
    v0_loaded_message "LoadedMessageV0",
    signatures BYTEA[],
    message_hash BYTEA,
    meta "TransactionStatusMeta",
    write_version BIGINT,
    updated_on TIMESTAMP NOT NULL,
    index BIGINT NOT NULL,
    CONSTRAINT transaction_pk PRIMARY KEY (slot, signature)
);

sqlc-generated Go code:

type Transaction struct {
	Slot            int64
	Signature       []byte
	IsVote          bool
	MessageType     pgtype.Int2
	LegacyMessage   sql.NullString
	V0LoadedMessage sql.NullString
	Signatures      [][]byte
	MessageHash     []byte
	Meta            sql.NullString
	WriteVersion    pgtype.Int8
	UpdatedOn       pgtype.Timestamp
	Index           int64
}

[pdewilde]

I ran into wanting this because I was using composite types in history tables. See my (now closed) issue #3148 for my motivating example.