Future support for record types?

[mscandal]

Came across this today:

Error: Postgres type 'record' is not supported by mapping

Example to produce error:

select (a,b)
from (
  select
    unnest(array['a','b','c']),
    unnest(array[1,2,3])
) s (a,b);

It would be super useful to be able to query by composite keys

[mattkrick]

Another use-case for records:

/*
  @name getEvent
  @param keys -> ((
    time, 
    place
  )...)
*/
SELECT * from "Events"
WHERE ("time", "place") in :keys;

[SebastienGllmt]

I was trying to do the same thing as @mattkrick and solved it by instead combining two arrays into a temporary table and then using it with in. This works and pgtyped infers the correct types as well

/* @name sqlTransactionOutput */
WITH pointers AS (
  SELECT tx_hash, output_index
  FROM
    unnest(
      (:tx_hash)::bytea[],
      (:output_index)::int[]
    ) x(tx_hash,output_index)
)
SELECT "TransactionOutput".payload
FROM
  "Transaction"
  INNER JOIN "TransactionOutput" ON "Transaction".id = "TransactionOutput".tx_id
WHERE ("Transaction".hash, "TransactionOutput".output_index) in (SELECT tx_hash, output_index FROM pointers);

[wokalski]

For custom named types, I quickly hacked a script to obtain the type definition of a record type from postgres's schema. It's obviously not trivial to incorporate.

The entry point is here: https://github.com/adelsz/pgtyped/blob/b00aa813ce308c61f9bf54bbe5c5de7fd64df09b/packages/cli/src/generator.ts#L149-L151

You'd have to plug the logic there to look up a "custom" type in the DB and generate its type definition. It's not that hard though. I guess the simplest thing to do would be to add custom types to TypeAllocator's mapping. Maybe a separate data structure is needed. Again, the architecture is not currently perfectly suited to do this lazy adding of types to the TypeMapper but it's not rocket science.

Here's my dumb script, it's obviously just a proof of concept and the only really relevant part is the SQL query from it.

script.ts

```typescript import { Pool } from 'pg'; // PostgreSQL database configuration (update with your details) const config = { database: 'caddie', }; // Initialize a connection pool const pool = new Pool(config); // Function to convert PostgreSQL data types to TypeScript types const convertToTypeScriptType = (pgType: string): string => { switch (pgType) { case 'int2': case 'int4': case 'int8': case 'numeric': case 'float4': case 'float8': return 'number'; case 'varchar': case 'text': case 'char': case 'bpchar': return 'string'; case 'bool': return 'boolean'; case 'date': case 'timestamp': case 'timestamptz': return 'Date'; default: return 'any'; // default fallback for unknown types } }; // Function to fetch custom record type schema and generate a TypeScript interface const generateTypeScriptInterfaceForCustomType = async (schemaName: string, typeName: string): Promise => { try { const client = await pool.connect(); const query = ` SELECT att.attname as "column", typ.typname as "dataType" FROM pg_type as base_typ INNER JOIN pg_namespace nsp ON nsp.oid = base_typ.typnamespace INNER JOIN pg_attribute att ON att.attrelid = base_typ.typrelid INNER JOIN pg_type typ ON att.atttypid = typ.oid WHERE base_typ.typtype = 'c' AND nsp.nspname = $1 AND base_typ.typname = $2 AND att.attnum > 0 AND NOT att.attisdropped; `; const res = await client.query(query, [schemaName, typeName]); let typeScriptInterface = `interface ${typeName.charAt(0).toUpperCase() + typeName.slice(1)} {\n`; res.rows.forEach(row => { const tsType = convertToTypeScriptType(row.dataType); typeScriptInterface += ` ${row.column}: ${tsType};\n`; }); typeScriptInterface += '}'; console.log(typeScriptInterface); client.release(); } catch (err) { console.error('Error generating TypeScript interface for custom type:', err); } }; // Example usage generateTypeScriptInterfaceForCustomType('scheduler', 'watchdog_attempt'); ``` as you can see by the comments no human would write, chatgpt with my little help actually wrote this