rpc function tell me to reload the schema cache

Question

I am creating an rpc function to get the number of likes for each post for a user, so I have created a function that takes userId as an argument, which is the uuid of the user that is in the session. But when I call the function on the front it returns me.

If a new function was created in the database with this name and parameters, try reloading the schema cache.

The function:

create function get_number_of_posts_by_user(userId uuid)
returns integer
as $$
  SELECT 
  count(pl.id)
  FROM 
  auth.users au
  join posts p on p.user_id = au.id
  join post_likes pl on pl.post_id = p.id
  where au.id = userId
$$ language sql;

Hey Ricardo. Have you tried to change the parameter type to `create function get_number_of_posts_by_user(user_id text)` then, on the where: `where au.id::text = user_id`? — Mansueli, Feb 14 '23 at 21:41
Hello @Mansueli my mistake was at the parameter that i used (it should not be writen in capital letter unles you use "variable") — Richi, Feb 14 '23 at 21:42

Erwin Brandstetter · Accepted Answer · 2023-02-14T22:53:29.790

get the number of likes for each post for a user

You need to group by post to get there.

CREATE OR REPLACE FUNCTION get_likes_per_post_for_user(_userid uuid)
  RETURNS TABLE (post_id uuid, likes bigint)  -- use actual type of post_id
  LANGUAGE sql AS
$func$
SELECT p.id, count(*)
FROM   posts      p 
JOIN   post_likes pl ON pl.post_id = p.id
WHERE  p.user_id = _userid
GROUP  BY 1
ORDER  BY 1;  -- or some other order?
$func$;

Call:

SELECT * FROM get_likes_per_post_for_user(<some_uuid>);

Major points:

You don't need to involve the table users at all. Filter by posts.user_id directly. Cheaper.
count(*) >> count(pl.id) in this case. A bit cheaper, too. count(*) has a separate implementation in Postgres.
count() returns bigint, not integer. Match what RETURNS declares one way or the other.
Avoid naming conflicts between function parameters and table columns. Prefixing _ for parameters (and never using the same for column names) is one common convention.
And table-qualify column names. In this case to avoid a conflict between the OUT parameter post_id (also visible in the query) and post_likes.post_id.
When counting the number of likes, don't call your function "get_number_of_posts...".

Your original issue may have been a XY problem that goes away with a proper function definition.

Addressing the title

If you actually need to reload the schema cache, use:

SELECT pg_stat_clear_snapshot();

The manual:

You can invoke pg_stat_clear_snapshot() to discard the current transaction's statistics snapshot or cached values (if any). The next use of statistical information will (when in snapshot mode) cause a new snapshot to be built or (when in cache mode) accessed statistics to be cached.

I have never had a need for this myself, yet. Typically, the problem lies elsewhere.

rpc function tell me to reload the schema cache

1 Answers1

Addressing the title