SupabaseS
Supabase•2y ago
SmallPeanut

How to transform query into function.

I am having trouble transforming my query into a function. Anyone see what I am doing wrong?

Here is my query:
UPDATE "FeaturedSections"
SET quiz_ids = (
  SELECT array_agg(quiz_id ORDER BY count DESC)
  FROM (
    SELECT quiz_id, COUNT(*) AS count
    FROM "UserVotes"
    WHERE created_at >= NOW() - INTERVAL '1 year'
      AND is_liked = true
    GROUP BY quiz_id
    ORDER BY count DESC
    LIMIT 10
  ) AS top_quizzes
)
WHERE title = 'Most Loved Quizzes'
returning
  quiz_ids;


Here is the function I made:
create or replace function update_featured_sections () returns table (quiz_ids bigint[]) as $$
BEGIN
  UPDATE "FeaturedSections"
  SET quiz_ids = (
    SELECT array_agg(quiz_id ORDER BY count DESC)
    FROM (
      SELECT quiz_id, COUNT(*) AS count
      FROM "UserVotes"
      WHERE created_at >= NOW() - INTERVAL '1 year'
        AND is_liked = true
      GROUP BY quiz_id
      ORDER BY count DESC
      LIMIT 10
    ) AS top_quizzes
  )
  WHERE title = 'Most Loved Quizzes'
  RETURNING quiz_ids;
END;
$$ language plpgsql;

After running the function, the row in the FeaturedSections table does not get updated.
Was this page helpful?