https://supabase.com/ logo
Join Discord
Powered by
Hey guys whats wrong with this function ```creat...
# sql
k
Hey guys whats wrong with this function
Copy code
create or replace function get_feed_posts() returns setof feed
as $func$ 

SELECT *
, (SELECT COUNT(*) FROM feed_activities WHERE feed_ref=feed.id and type = 'like') as LIKE_COUNT
, (SELECT COUNT(*) FROM feed_activities WHERE feed_ref=feed.id and type = 'comment') as COMMENT_COUNT
FROM feed

$func$
language sql;
c
You defined the function to return rows from the feed table but you are actually returning tuples that carry additional information (i.e. to each feed row, you attach 2 more count fields)
k
okay, what i want to do here is to extend that table with count of comments and likes from another table
i have feed_activities table, where i have type: 'comment' or type: 'like'
c
you have 2 options
k
and i need to get count to show on feed card
c
one is to declare all fields in the return type, something like this
Copy code
CREATE FUNCTION validate_functions()
RETURNS TABLE(
  feed_col1 type,
  feed_col2 type,
  ...
  feed_coln type,
  extra_count1 bigint,
  extra_count2 bigint

)
BEGIN...
The problem here is that everytime you make a change to the feed table, you would need to update the return type
There is another, shorter syntax but I don't remember exactly how it works - it allows you to specify the table record type and then to augment it
something like
Copy code
CREATE FUNCTION validate_functions()
RETURNS TABLE(
  feed,
  extra_count1 bigint,
  extra_count2 bigint

)
BEGIN...
but I am not sure exactly what the syntax was...if you search stackoverflow you will find it, that's where I've seen it
k
hm i am not an expert in SQL 🙂 it will be hard for me to understand
c
The section called "Various complete table types"
k
hm is there any other way to get comment_count and like_count from table 
feed_activities
c
An alternative would be to create a view
something like this
Copy code
CREATE VIEW feed_posts WITH (security_barrier) AS
SELECT *
, (SELECT COUNT(*) FROM feed_activities WHERE feed_ref=feed.id and type = 'like') as LIKE_COUNT
, (SELECT COUNT(*) FROM feed_activities WHERE feed_ref=feed.id and type = 'comment') as COMMENT_COUNT
FROM feed;
then you call this from the supabase js client as if it were a table called feed_posts
NOTE: the WITH (security_barrier) modifier is an implmentation detail that you need if you are using RLS
PreviousNext
Powered by