© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
SupabaseS
Supabase•3y ago•
2 replies
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;
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;
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.
Supabase banner
SupabaseJoin
Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.
45,816Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements

Similar Threads

How to import local code into Deno function?
SupabaseSSupabase / help-and-questions
4y ago
How to query arrays?
SupabaseSSupabase / help-and-questions
4y ago
Query table from edge function
SupabaseSSupabase / help-and-questions
4y ago
How to delete Edge function?
SupabaseSSupabase / help-and-questions
3mo ago