Postgresql: Error executing select into statement within a function

Question

Consider the following statement:

create function get_most_frequent_topic(out topic_id integer)
as $$
begin
    select 
        p.topic_id into topic_id,
        count(*) as freq
    from post p
    group by p.topic_id
    order by count(*) desc
    limit 1;
end;
$$ language plpgsql

When I execute, I get this error:

Error occurred during SQL query execution

Reason:
SQL Error [42601]: ERROR: "count" is not a known variable
  Position: 123

However,if I remove count(*) from select, then it starts working. Why?

create function get_most_frequent_topic(out topic_id integer)
as $$
begin
    select 
        p.topic_id into topic_id        
    from post p
    group by p.topic_id
    order by count(*) desc
    limit 1;
end;
$$ language plpgsql

Answer 1

The correct syntax for using into is to list all the selected values then all the variables, like this:

create function get_most_frequent_topic(out topic_id integer)
as $$
begin
    select p.topic_id, count(*)
    into topic_id, freq
    from post p
    group by p.topic_id
    order by count(*) desc
    limit 1;
end;
$$ language plpgsql

which would of course fail because you haven't defined the variable freq to hold the count(*) value.

Note that providing an alias for count(*) has no effect.

Your query has , count(*) as freq where from is expected.