PostgreSQL error that I can't figure out

Question

I have this SQL Query which should find the rank of a certain cook. I should compute the number of recipes this cook has made, compute the number of other cooks recipes, and the final goal is to get our cook ranking, which is the number of cooks who did less recipes then he did. (Sorry for my bad English and the long explanation).

The query:

 /*Compute Cooking Rank*/
 create or replace function
 ComputeCookingRank(u integer)
 returns integer as $$
 declare
 uidSum record; 
 uSum integer;
 ranking integer; 
 begin 
 select distinct uid,count(uid) as "sum" into uidSum from HasCooked group by uid;
 ---> select distinct into uSum from uidSum where uid=u;
 select count(uid) into ranking from uidSum where uid=u and sum<uSum;
 end;
 $$ language plpgsql;

The problematic line is the one with the arrow; is it ok to take data from a 'record' type variable like this? What am I doing wrong here?

I don't think it is material to the error you're getting, but you really don't need the DISTINCT when you are grouping. — Jonathan Leffler
– Jonathan Leffler, Commented Dec 9, 2011 at 0:03
It isn't clear what your uidSum is a record of. Use the simple variables, uSum and ranking, in the INTO clause: INTO uSum, ranking. There's a very good chance that will work. (I simply don't know about the record notation in PostgreSQL, which is one reason this is a comment rather than an answer.) — Jonathan Leffler
– Jonathan Leffler, Commented Dec 9, 2011 at 0:06
You should also post the exact error message you get from the SQL you posted; it makes it much easier for people to help you. — Jonathan Leffler
– Jonathan Leffler, Commented Dec 9, 2011 at 0:09
Your function goes way wrong before it comes to that minor syntax error. See my comprehensive answer. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 9, 2011 at 0:49

Bill Karwin · Accepted Answer · 2011-12-09 00:04:32Z

2

select distinct /* you list no columns here */ into uSum from uidSum where uid=u;

answered Dec 9, 2011 at 0:04

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Comments

Erwin Brandstetter · Accepted Answer · 2011-12-09 00:43:23Z

First of all, you don't need a plpgsql function for that. A plain query does the job. A CTE helps in this case:

WITH x AS (
    SELECT uid, count(*) as ct
    FROM   hascooked
    GROUP  BY uid
    )
SELECT count(*) AS cooks_with_fewer_recipies
FROM   x
WHERE  ct < (SELECT ct FROM x WHERE uid = u);

Next, an number of things is wrong with this statement:

select distinct uid,count(uid) as "sum" into uidSum from HasCooked group by uid;

These points are just my advice, not strictly wrong:

You already GROUP BY uid, a DISTINCT is pointless in this particular case.
Don't use the name of a function (sum) as column name. Only leads to problems.
With a proper name, you don't need double quotes ("sum"). Just my advice, not strictly wrong.
You can use mixed case identifiers, but don't. Identifiers are folded to lower case if not double-quoted. Read about identifiers in the manual.
As you GROUP BY uid, it is better to use count(*) instead of count(uid). Slightly faster, and better results in the (unlikely?) case that uid could be NULL - then you get a count for the NULL- cases, too.

Cleaned up form (still wrong!):

SELECT uid, count(*) as ct INTO uidSum
FROM   hascooked
GROUP  BY uid;

The statement is still wrong, because you try to assign multiple rows to the single variable uidSum, which is not possible.

A record can hold multiple columns, not multiple rows. You need a table for that or aggregate the rows into one.

Only the first row will be assigned, which is picked at random as you have no ORDER BY. DISTINCT used to guarantee that the result set is ordered by the DISTINCT columns, but this is not true any more since version 8.4. I quote the release notes for 8.4

SELECT DISTINCT and UNION/INTERSECT/EXCEPT no longer always produce sorted output (Tom)

In any case, ordering by uid is as nonsensical as retrieving a single row in this context. It is obviously not what you want. One other way would be to loop through the resulting rows, one by one. More about loops here.

The proper solution is my query above.

Collectives™ on Stack Overflow

PostgreSQL error that I can't figure out

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related