Scanning of composite types -- is it possible?

[pdewilde]

What do you want to change?

I've been trying to solve a problem and haven't been able to find an elegant way of doing it.

Here is what I am trying to do (using postgres with pgx v5):

I have some table

CREATE TABLE  foos (
    id BIGSERIAL PRIMARY KEY,
    title TEXT,
    description TEXT
 );

And I want to track the history, so I created a trigger:

CREATE TABLE IF NOT EXISTS foos_history
(
   id           BIGSERIAL NOT NULL,
   foo_id BIGINT NOT NULL,
   changed_at   TIMESTAMP DEFAULT timezone('utc', now()) NOT NULL,
   new_value    foos,
   operation    TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_id_time ON foos_history (foo_id, changed_at);
CREATE INDEX IF NOT EXISTS idx_time_id ON foos_history (changed_at, foo_id);

CREATE OR REPLACE FUNCTION foos_history_func() RETURNS TRIGGER AS
$foo_history$
BEGIN
   IF (tg_op = 'DELETE' OR tg_op = 'UPDATE') THEN
       INSERT INTO foos_history (foo_id, changed_at, new_value,
                                       operation)
       SELECT old.id, timezone('utc', now()), new, tg_op;
   ELSEIF (tg_op = 'INSERT') THEN
       INSERT INTO foos_history (foo_id, changed_at, new_value,
                                       operation)
       SELECT new.id, timezone('utc', now()), new, tg_op;
   END IF;
   RETURN NULL; -- this is an AFTER trigger. Return result ignored.
END;
$foo_history$ LANGUAGE plpgsql;

-- NOTE: TRUNCATE cannot be triggered on with a row level trigger
-- and will not be saved in history currently.
CREATE TRIGGER foos_history_trigger
   AFTER INSERT OR UPDATE OR DELETE
   ON foos
   FOR EACH ROW
EXECUTE FUNCTION foos_history_func();

In the foos_history table, the new_value is the composite foos type which is implicitly defined by the foos table. https://www.postgresql.org/docs/current/rowtypes.html

I wanted to generate query that would allow me to read the history table:

-- name: ListFoosHistory :many
SELECT * FROM foos_history WHERE
    (sqlc.narg(filter_id)::bigint IS NULL OR sqlc.narg(filter_id)::bigint = foo_id)
    AND (sqlc.narg(start_timestamp)::timestamp IS NULL OR changed_at >= sqlc.narg(start_timestamp)::timestamp)
    AND (sqlc.narg(end_timestamp)::timestamp IS NULL OR changed_at <= sqlc.narg(end_timestamp)::timestamp)
ORDER BY
    id
LIMIT @limit_
OFFSET @offset_
;

I tried overriding the type of new_value to be a foo in my sqlc.yaml:

          - column: 'foos_history.new_value'
            go_type:
              type: '*Foo'

But the generated code doesn't know how to map the text representation of a foo composite into a golang Foo struct. I don't see any good way to do that without manually writing a scanner interface for Foo that works from the composite text interface. (though that would be brittle, I would love to be able to generate it somehow).

For reference, the error I get back from sqlc is:

cannot scan unknown type (OID 16396) in text format into **db.Foo

I know pgxtypes has https://pkg.go.dev/github.com/thoohv5/pgx/pgtype#CompositeIndexScanner and https://pkg.go.dev/github.com/thoohv5/pgx/pgtype#CompositeFields but documentation is light, without much discussion the web that I could find. Another option I thought was to use postgres inheritance rather than embedding the field, but I don't think that would be as elegant.

Would appreciate any thoughts.

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[pdewilde]

Looks like this is sort of a duplicate of #2760. I'll close this so the discussion can be centralized.