PostgreSQL: subquery must return one column

Question

Looking obvious error, still see no chance to find it. I've made to localize error in this function:

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;

Which uses this type:

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

And I'm getting this error in runtime:

Error: subquery must return only one column

You have, in fact, two queries. Did you try each separately? Which one is triggering the error? — FDavidov
– FDavidov, Commented Nov 1, 2016 at 12:41
You either select into as in @Juan answer or do question := (select ("a"."id", ...) from ...) — Clodoaldo Neto
– Clodoaldo Neto, Commented Nov 1, 2016 at 12:48
btw no need to use so many "" unless you have uppercase and spaces in the variables name. So suggestion dont use uppercase or spaces to keep code simple. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 1, 2016 at 12:49

Clodoaldo Neto · Accepted Answer · 2016-11-01 13:07:20Z

3

I would just get rid off all the complexity and make it plain 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;

edited Nov 1, 2016 at 13:07

answered Nov 1, 2016 at 12:59

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lazyexpert Over a year ago

thanks for suggestion, this definitely looks cleaner than my sollution

Juan Carlos Oropeza · Accepted Answer · 2016-11-01 12:40:25Z

1

with my type

CREATE TYPE map.get_near_link AS
   (link_id integer,
    distance integer,
    direction integer,
    geom public.geometry(4));

I do

sRow map.get_near_link;

SELECT i.Link_ID, i.Distance, i.Direction, i.geom into sRow 
FROM 
    index_query i;

answered Nov 1, 2016 at 12:40

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

1 Comment

Lazyexpert Over a year ago

Thanks, this did the job ;) And another thanks for the "" advice.

Collectives™ on Stack Overflow

PostgreSQL: subquery must return one column

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related