cyberia/research/mimi/sql/queries/telegram.sql

sql
ฯ€ 0.0% 267 words ยท 2 min 
-- name: FindTelegramPeers :many
SELECT
    id,
    chat_name
FROM
    telegram_peer
WHERE
    enabled;

-- name: FindTelegramPeersWithTopics :many
SELECT
    p.id AS chat_id,
    p.chat_name AS chat_name,
    p.description AS chat_description,
    t.id AS topic_id,
    t.title AS topic_title,
    t.description AS topic_description
FROM
    telegram_peer p
    JOIN telegram_topic t ON t.peer_id = p.id
WHERE
    p.enabled;

-- name: FindTelegramMessages :many
SELECT
    m.message,
    p.chat_name AS chat_name,
    t.title AS topic_title
FROM
    telegram_message m
    INNER JOIN telegram_peer p ON m.peer_id = p.id
    JOIN telegram_topic t ON m.topic_id = t.id
WHERE
    m.created_at >= $1;

-- name: SaveTelegramTopic :exec
INSERT INTO
    telegram_topic (id, peer_id, title, description)
VALUES
    ($1, $2, $3, $4) ON conflict (id, peer_id) DO
UPDATE
SET
    title = excluded.title,
    description = excluded.description;

-- name: SaveTelegramMessage :exec
INSERT INTO
    telegram_message (id, peer_id, topic_id, message, created_at)
VALUES
    ($1, $2, $3, $4, $5);

-- name: TelegramTopicExists :one
SELECT
    1
FROM
    telegram_topic
WHERE
    peer_id = $1
    AND id = $2;

-- name: GetTelegramTablesSchema :many
SELECT
    t.table_name,
    obj_description(
        (
            quote_ident(t.table_schema) || '.' || quote_ident(t.table_name)
        )::regclass::oid
    ) AS table_comment,
    array_agg(
        c.column_name || ' ' || c.data_type || CASE
            WHEN c.is_nullable = 'NO' THEN ' NOT NULL'
            ELSE ''
        END || CASE
            WHEN c.column_default IS NOT NULL THEN ' DEFAULT ' || c.column_default
            ELSE ''
        END || CASE
            WHEN col_description(
                (
                    quote_ident(c.table_schema) || '.' || quote_ident(c.table_name)
                )::regclass::oid,
                c.ordinal_position
            ) IS NOT NULL THEN ' -- ' || col_description(
                (
                    quote_ident(c.table_schema) || '.' || quote_ident(c.table_name)
                )::regclass::oid,
                c.ordinal_position
            )
            ELSE ''
        END
        ORDER BY
            c.ordinal_position
    ) AS COLUMNS
FROM
    information_schema.tables t
    JOIN information_schema.columns c ON t.table_name = c.table_name
    AND t.table_schema = c.table_schema
WHERE
    t.table_schema = 'public'
    AND t.table_name LIKE 'telegram_%'
GROUP BY
    t.table_name,
    t.table_schema
ORDER BY
    t.table_name;

Graph