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

What is the point of selecting count(*) if it doesn't go into an out variable? — Tim Biegeleisen
– Tim Biegeleisen, Commented May 29, 2024 at 1:38
Im a leaning the psql functions. Kindly tell me what's wrong? — Cody
– Cody, Commented May 29, 2024 at 1:49

Bohemian · Accepted Answer · 2024-05-29 01:58:25Z

2

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.

answered May 29, 2024 at 1:58

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

Collectives™ on Stack Overflow

Postgresql: Error executing select into statement within a function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related