Hello 👋 ! I am messing around with testing functions, but am having trouble getting a response and I am unsure why.

From PostgREST docs (Supabase uses) CREATE FUNCTION getallfilms() RETURNS SETOF films AS $$ SELECT * FROM films; $$ LANGUAGE SQL IMMUTABLE;

so I have to return something?

returns setof "solana..." as

but it let me select return type void for the function - do I need to change that to records?

Using the UI? That might be what it uses.

I am using the UI within Supabase

BUT you need to change the name of the function or delete the old one first...

What do you mean?

You have to return a table or records array, not sure the UI does that... here is another example I found... create or replace function get_tags (tag text) returns setof products_view language plpgsql as $$ begin return query select * from products_view where tag % any(categories); end; $$

hmm

you would also not have (tag text) as you are not passing a variable to the function

If I create a function in the SQL Editor, is there a way to call it in my javascript?

I think this is what you want create or replace function test1() returns setof films language plpgsql as $$ begin return query select * from films; end; $$

Not sure what you mean

rpc does not depend on the UI. You can create function in UI-function section or in the SQL editor

so if I put that into the SQL editor, how do I reference it in my JS?

test1() rpc call

like this?

javascript
  const test = () => {
    supabase.rpc("test1").then(({ error, data, count, status, statusText }) => {
      if (error) console.error({ error });
      else console.log({ data });
    });
  };
  test();

yes

So in the SQL Editor when I run

create or replace function test1()
  returns setof "SolanaFloorTracker"
  language plpgsql
  as $$
    begin
    return query
      select * from "SolanaFloorTracker";
    end;
  $$

it says 'success. no rows returned'

Do you have rls on?\

not enabled currently

do select * test1() in the sql editor

should the select * test1() in the editor be in a new query?

sorry select test1()

Ah it outputs a table but it seems funky.

sorry for the round abouts, so many ways to write a function...

No worries. I am just learning as well 🙂

I've not used views, but they are just a different presentation of your table data.

Gotcha. Someone else wrote this. Its a bit above my SQL level

No idea on that view code but it looks pretty odd to me. Just remember with your table function you can do alot more processing on the table (sorting/filtering) etc. and return far less than the full table.

I suppose I need to learn more about SQL. I'm not too sure how I can write a query on the table that would group by name and then order by timestamp and get the two most recent entries...

https://spin.atomicobject.com/2016/03/12/select-top-n-per-group-postgresql/ some hints

I think I set up a function similar to what you described

create or replace function fetch_data()
  returns setof "SolanaFloorTracker"
  language plpgsql
  as $$
    begin
    return query
      SELECT rank_filter.* 
      FROM (
        SELECT "SolanaFloorTracker".*, 
        rank() OVER (
            PARTITION BY "CollectionName"
            ORDER BY created_at DESC
        )
        FROM "SolanaFloorTracker"
      ) rank_filter WHERE RANK <=2;
    end;
  $$

but when I call

select fetch_data

it says "structure of query does not match function result type"

try setof rank_filter, but I was just trying to point you in the right direction. I believe somewhere in that article and function is your solution, but I can't really get you much further without doing research.

when trying setof rank_filter when I try to run it it says 'type "rank_filter" does not exist'

It appears the issue is the setof "" gets messed up as the result comes back processed by the rankfilter stuff. Either play with it using some stuff here: https://www.postgresql.org/docs/9.2/xfunc-sql.html starting at 35.4.8. SQL Functions Returning Sets, or ask in the sql section showing your function with the returns setof "SolanaF...." and the "structure of query does not match" error. Without playing with it myself, I would have to learn what syntax is needed. You could also as a test run everything after return up to end in the SQL editor to verify at least that part does what you want.