pgtyped Future support for record types?

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

Aug 25 '21 22:08 mscandal

Another use-case for records:

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

Sep 17 '21 22:09 mattkrick

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

May 06 '22 05:05 SebastienGllmt

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

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<void> => {
    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

Nov 15 '23 16:11 wokalski

pgtyped pgtyped copied to clipboard

Future support for record types?

pgtyped
pgtyped copied to clipboard