如何根据另一列数据类型在SELECT语句中设置字面量列值

一、动态生成统一类型的SELECT语句（表级判断） #

如果你的需求是根据表中properties列内指定键的类型（全表统一），生成对应字面量的静态查询，可以用PL/pgSQL的动态SQL实现：

1. 匿名块执行查询 #

DO $$
DECLARE
    target_type text;
    query_text text;
BEGIN
    -- 获取properties中目标键的类型（这里以'your_key'为例，取第一行的类型）
    SELECT pg_typeof((properties->>'your_key')::jsonb) INTO target_type
    FROM g_node LIMIT 1;

    -- 根据类型拼接查询语句
    IF target_type = 'text' THEN
        query_text := 'SELECT ''a'' AS sortColumn FROM g_node';
    ELSIF target_type = 'numeric' THEN
        query_text := 'SELECT 1 AS sortColumn FROM g_node';
    ELSE
        RAISE EXCEPTION '不支持的类型: %', target_type;
    END IF;

    -- 执行生成的查询
    EXECUTE query_text;
END $$;

2. 封装成函数返回结果 #

如果需要直接获取查询结果，可创建函数：

CREATE OR REPLACE FUNCTION get_sorted_nodes()
RETURNS TABLE(sortColumn text)
LANGUAGE plpgsql
AS $$
DECLARE
    target_type text;
    query_text text;
BEGIN
    SELECT pg_typeof((properties->>'your_key')::jsonb) INTO target_type
    FROM g_node LIMIT 1;

    IF target_type = 'text' THEN
        query_text := 'SELECT ''a''::text AS sortColumn FROM g_node';
    ELSIF target_type = 'numeric' THEN
        query_text := 'SELECT 1::text AS sortColumn FROM g_node';
    ELSE
        RAISE EXCEPTION '不支持的类型: %', target_type;
    END IF;

    RETURN QUERY EXECUTE query_text;
END $$;

调用方式：SELECT * FROM get_sorted_nodes();

二、每行动态返回对应类型（列类型统一兼容） #

如果表中每行properties的目标键类型不统一，PostgreSQL要求同一列必须是相同类型，因此只能用以下兼容方案：

1. 返回JSON类型（保留原生类型） #

SELECT 
    CASE 
        WHEN pg_typeof((properties->>'your_key')::jsonb) = 'text' THEN '"a"'::jsonb
        WHEN pg_typeof((properties->>'your_key')::jsonb) = 'numeric' THEN '1'::jsonb
    END AS sortColumn
FROM g_node;

查询结果为JSON类型，可在应用层解析出对应的字符串或数字。

2. 拆分双列返回 #

返回两个分别对应字符串和数字类型的列，根据实际类型取对应列的值：

SELECT 
    CASE WHEN pg_typeof((properties->>'your_key')::jsonb) = 'text' THEN 'a' END AS sort_str,
    CASE WHEN pg_typeof((properties->>'your_key')::jsonb) = 'numeric' THEN 1 END AS sort_num
FROM g_node;

三、Python脚本生成查询（简单直接） #

如果SQL方案过于复杂，用Python拼接查询语句更灵活：

import psycopg2

# 连接数据库
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pwd host=your_host")
cur = conn.cursor()

# 获取目标键的类型
cur.execute("SELECT pg_typeof((properties->>'your_key')::jsonb) FROM g_node LIMIT 1;")
target_type = cur.fetchone()[0]

# 生成查询语句
if target_type == 'text':
    sql = "SELECT 'a' AS sortColumn FROM g_node;"
elif target_type == 'numeric':
    sql = "SELECT 1 AS sortColumn FROM g_node;"
else:
    raise ValueError(f"不支持的数据类型: {target_type}")

# 执行并获取结果
cur.execute(sql)
results = cur.fetchall()

# 处理结果
for row in results:
    print(row)

# 关闭连接
cur.close()
conn.close()

内容的提问来源于stack exchange，提问作者w. Patrick Gale