https://supabase.com/ logo
#sql
Title
# sql
m

MDobs

03/22/2022, 2:43 PM
what type would you add on a Function to return all rows of a table? meaning the result of running this
SELECT * FROM users
s

Scott P

03/22/2022, 2:44 PM
SETOF users
(if your table is users). May need to clarify it with a schema e.g.
SETOF public.users
m

MDobs

03/22/2022, 2:46 PM
yes that is code wise, but what do you pick on the dropdown when creating a Function, I don't think there is a
setof
option
or it doesn't matter?
s

Scott P

03/22/2022, 2:48 PM
Oh, if you're doing it via the functions page of the dashboard, I think
record
returns the correct type
m

MDobs

03/22/2022, 2:49 PM
with record I get
"message":"input of anonymous composite types is not implemented","code":"0A000"
with
Copy code
var users = plv8.execute(
  `select * from users`
);

return users
s

Scott P

03/22/2022, 2:50 PM
Why are you doing a query like that via plv8? PLV8 is basically for JS code that can run inside postgres afaik.
m

MDobs

03/22/2022, 2:51 PM
yes agreed, I plan to do more in there. Just trying out something basic, but I can't really get it off the ground 😦
"downgrading" the primary key
id
from int8 to int4 make it possible to get some json data with the above code. otherwise a
TypeError: Do not know how to serialize a BigInt
s

Scott P

03/22/2022, 2:59 PM
I would honestly spend the time learning how to write function using SQL if you're planning on doing anything beyond the basics (unless you already know how to do that). For example, this works:
Copy code
sql
create or replace function select_users_v8() 
returns SETOF auth.users as $$

    var num_affected = plv8.execute(
        'SELECT * FROM auth.users'
    );

    return num_affected;
$$ language plv8;

SELECT * FROM select_users_v8()