Error to postgresql

Question

Pls Help!

count_rate := 
(
SELECT COUNT(trate.rid) AS count_rate 
FROM tlot LEFT JOIN trate ON trate.ridlot = tlot.lid 
GROUP BY tlot.lid
);

FULL:

CREATE FUNCTION editstatuswait()
  RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE count_rate INTEGER;
BEGIN
  count_rate := (SELECT COUNT(trate.rid) AS count_rate FROM tlot LEFT JOIN trate ON trate.ridlot = tlot.lid GROUP BY tlot.lid);

  IF (count_rate != 0) THEN
    UPDATE tlot SET lstatus = 3
    WHERE tlot.lexpirationdate < NOW()
    AND tlot.lexpirationdate > NOW()-INTERVAL '24 hours' AND tlot.lstatus = 2;
  ELSE
    UPDATE tlot SET lstatus = 0
    WHERE tlot.lexpirationdate < NOW()
    AND tlot.lexpirationdate > NOW()-INTERVAL '24 hours' AND tlot.lstatus = 2;
  END IF;
END;
$$;

ERROR: [21000] ERROR: more than one row returned by a subquery used as an expression Где: SQL statement SELECT (SELECT COUNT(trate.rid) AS count_rate FROM tlot LEFT JOIN trate ON trate.ridlot = tlot.lid GROUP BY tlot.lid

I can not understand how to get rid of this error...

score 0 · Answer 1 · answered Mar 01 '18 at 21:16

0

Remove the GROUP BY:

count_rate := (SELECT COUNT(trate.rid) AS count_rate FROM tlot LEFT JOIN trate ON trate.ridlot = tlot.lid);

Of course, this may not do what you intend. It will at least fix the error.

answered Mar 01 '18 at 21:16

Gordon Linoff

1,242,037
58
646
786

The error corrected, but yes, it does not do what it takes :( – Maksim Parkhomenko Mar 01 '18 at 21:23
@MaksimParkhomenko, check this url to learn [how to loop one data set](https://stackoverflow.com/questions/22339628/cursor-based-records-in-postgresql) – Sphinx Mar 01 '18 at 21:30

score 0 · Accepted Answer · answered Mar 02 '18 at 09:06

The solution will depend on what you are trying to achieve.

A variable can only contain a single value, so your attempt to store the result of a subselect that returns more than one row in count_rate is bound to fail.

You will have to come up with a subselect that returns at most one row (if it returns no row, NULL will be assigned to the variable).

If you are only interested in the first row (unlikely, since there is no ORDER BY), you could append LIMIT 1 to the query.
If you want only the count for a certain tlot.lid, you should use WHERE tlot.lid = ... instead of a GROUP BY.
If you want to process multiple results, you would use a construction like:
```
FOR count_rate IN SELECT ... LOOP
   ...
END LOOP;
```

Error to postgresql

2 Answers2