如何从PostgreSQL函数返回临时表？报错问题求助

尝试用PostgreSQL搭建运行模拟任务的简单任务队列，运行request_jobs函数时持续抛出错误：

Array value must start with "{" or dimension information. malformed array literal: "1"

推测问题出在函数内的数组处理逻辑上。

数据库Schema #

runner表 #

CREATE TABLE runner (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT clock_timestamp() NOT NULL,
    last_seen_at timestamp with time zone,
    alias character varying(255) NOT NULL,
    hostname character varying(255) NOT NULL
);

CREATE SEQUENCE runner_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE runner_id_seq OWNED BY runner.id;
ALTER TABLE ONLY runner ALTER COLUMN id SET DEFAULT nextval('runner_id_seq'::regclass);
ALTER TABLE ONLY runner ADD CONSTRAINT runner_pkey PRIMARY KEY (id);

job表 #

CREATE TYPE public.job_status AS ENUM (
    'pending',
    'failed',
    'complete',
    'running'
);
CREATE TABLE job (
    id integer NOT NULL,
    created_at timestamp with time zone DEFAULT clock_timestamp() NOT NULL,
    completed_at timestamp with time zone,
    status public.job_status DEFAULT 'pending'::public.job_status NOT NULL,
    specification jsonb NOT NULL,
    upstream_manifest integer,
    completed_by integer,
    attempted_by integer
);

CREATE SEQUENCE job_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE job_id_seq OWNED BY job.id;
ALTER TABLE ONLY job ALTER COLUMN id SET DEFAULT nextval('job_id_seq'::regclass);
ALTER TABLE ONLY job
    ADD CONSTRAINT job_pkey PRIMARY KEY (id);
ALTER TABLE ONLY job
    ADD CONSTRAINT job_attempted_by_fkey FOREIGN KEY (attempted_by) REFERENCES runner(id);
ALTER TABLE ONLY job
    ADD CONSTRAINT job_completed_by_fkey FOREIGN KEY (completed_by) REFERENCES runner(id);

初始化数据 #

INSERT INTO runner (alias, hostname) VALUES ('test01', 'test-host');

INSERT INTO job (specification) VALUES ('{"spec": true}'::jsonb);

出错的request_jobs函数 #

-- 1. 通过alias获取runner ID
-- 2. 更新runner最后活跃时间（触发器？）
-- 3. 选取指定数量的pending状态任务
-- 4. 设置尝试执行任务的runner外键
CREATE OR REPLACE FUNCTION public.request_jobs(runner_alias text, total_jobs integer)
RETURNS TABLE(job_id integer, specification json) AS $$
DECLARE
    runner_id integer;
    selected_jobs job[];
BEGIN
    -- 开启事务
    BEGIN
        -- 根据runner_alias获取runner_id
        SELECT id INTO runner_id FROM runner WHERE alias = runner_alias;

        -- 选取前total_jobs个pending状态任务分配给runner
        SELECT j.id as job_id, j.specification
        FROM job AS j
        WHERE status = 'pending'
        LIMIT total_jobs
        FOR UPDATE SKIP LOCKED
        INTO selected_jobs;

        -- 更新选中任务的状态为running并分配给runner
        UPDATE job
        SET attempted_by = runner_id,
            status = 'running'
        WHERE id IN (SELECT job_id FROM selected_jobs);

        -- 返回选中的任务
        RETURN QUERY SELECT s.job_id, s.specification FROM selected_jobs as s;
    END;

END;
$$ LANGUAGE plpgsql;

问题根源与修复 #

问题原因 #

selected_jobs被定义为job[]（job表的行类型数组），但代码中用SELECT j.id, j.specification INTO selected_jobs返回的是两列独立数据，无法直接存入行类型数组，导致数组格式解析错误。

修复后的函数 #

直接合并任务选取、更新与返回逻辑，避免数组操作：

CREATE OR REPLACE FUNCTION public.request_jobs(runner_alias text, total_jobs integer)
RETURNS TABLE(job_id integer, specification jsonb) AS $$
DECLARE
    runner_id integer;
BEGIN
    -- 获取对应runner的ID
    SELECT id INTO runner_id FROM runner WHERE alias = runner_alias;

    -- 锁定pending任务并更新状态，同时返回结果
    RETURN QUERY
    UPDATE job
    SET attempted_by = runner_id,
        status = 'running'
    WHERE id IN (
        SELECT id
        FROM job
        WHERE status = 'pending'
        LIMIT total_jobs
        FOR UPDATE SKIP LOCKED
    )
    RETURNING id AS job_id, specification;

    -- 更新runner最后活跃时间
    UPDATE runner
    SET last_seen_at = clock_timestamp()
    WHERE id = runner_id;
END;
$$ LANGUAGE plpgsql;

修复说明 #

1. 移除了容易出错的selected_jobs数组，用子查询锁定目标任务，在UPDATE语句中完成状态修改与runner分配
2. 使用RETURNING子句直接返回被更新的任务数据，无需额外查询步骤
3. 补充了原注释中提到的更新runner最后活跃时间的逻辑
4. 将返回的specification类型调整为jsonb（与表中字段类型一致，避免隐式转换问题）

内容的提问来源于stack exchange，提问作者ijustlovemath