PostgreSQL:子查询必须返回一列

Question

看起来很明显的错误，仍然看不到找到它的机会。我已经在这个函数中定位了错误:

CREATE OR REPLACE FUNCTION findRecipientsByQuestion(questionId BIGINT)
RETURNS SETOF BIGINT AS $$
DECLARE
  question questionObject;  

  BEGIN
    question := (
      SELECT "a"."id", "a"."body", "a"."author_id", "a"."category_id", "a"."urgent", "a"."created_at", "a"."locale_id", "a"."lat", "a"."lng", "a"."radius" 
      FROM "question" "a" 
      WHERE "a"."id"=questionId 
      LIMIT 1
    );


    RETURN QUERY SELECT "a"."id" 
    FROM "user" "a" INNER JOIN "notifications" "b" ON ("a"."id"="b"."user_id")
    WHERE ("b"."category_id"=question.category_id OR "b"."urgent") AND 
        isGeoMatch("a"."lat", "a"."lng", "a"."radius", question.lat, question.lng, question.radius);
  END 
  $$LANGUAGE plpgsql;

哪个使用了这个类型:

CREATE TYPE questionObject AS (
  id BIGINT,
  body VARCHAR,
  author_id BIGINT,
  category_id BIGINT,
  urgent BOOLEAN,
  created_at TIMESTAMP,
  locale_id BIGINT,
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION,
  radius INTEGER
);

我在运行时遇到这个错误:

Error: subquery must return only one column

Answer 1

我只想摆脱所有的复杂性，让它成为简单的 sql:

create or replace function findrecipientsbyquestion (
    _questionid bigint
) returns setof bigint as $$

    select a.id 
    from
        user a
        inner join
        notifications b on a.id = b.user_id
        inner join (
            select categoty_id, lat, lng, radius 
            from question
            where id = _questionid 
            limit 1
        ) q on q.category_id = b.category_id or b.urgent
    where isgeomatch(a.lat, a.lng, a.radius, q.lat, q.lng, q.radius);

$$ language sql;