https://supabase.com/ logo
Join Discord
Powered by
Yeah, that's not a stored function. Just because i...
# help
s
Yeah, that's not a stored function. Just because it's stored inside the web-based query editor, it doesn't mean it's a stored function inside the DB. A stored function looks something like this:
Copy code
sql
CREATE OR REPLACE FUNCTION public.some_function()
RETURNS boolean
LANGUAGE plpgsql AS $$
BEGIN
  --  Your query code goes here
END;
$$;
The open and saved queries on the dashboard are saved in your browser, and only really exist to make it easier for you to run common queries. Take a look at https://www.postgresqltutorial.com/postgresql-create-function/ for more details on creating functions. Gonna open a thread from here if you want to discuss further 🙂
a
ohh
im trying from morning
is that psql syntax ?
s
Yeah, it is. PG functions can be a little frustrating at times, especially if you've never written them before, but they can allow you to do things that might not be easy (or possible, in some cases) using the JS library
a
ah isee, thank you so much
in between BEGIN and END can i use normal PostgreSql syntax?
s
In general, yes. There's some gotchas - you might need to nest queries inside each other in advanced examples. The return type (where it says 
RETURN BOOLEAN
in the example above) needs to match the output from the query. As a simple example, if I wanted to find a user by their ID using a stored function, I could do something like this:
Copy code
sql
CREATE OR REPLACE FUNCTION public.get_user_by_id(user_id uuid)
RETURNS SETOF public.users
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
    SELECT * FROM public.users users
    WHERE users.id = user_id
  RETURNING *;
END;
$$;
a
wooah quick question is public necessary ? in here
s
It's advisable. If the table name exists in multiple schemas (e.g. 
auth.users
and 
public.users
), the function might not know which table you're referring to
a
umm, i think i have a problem what if im creating a new table not doing query on already present tables
is that doable
s
Yeah, it should be. If you're willing to share your query, I can try and adjust it to work as a stored function.
In general, I wouldn't allow tables to be created from a function though because it might be a nightmare to manage later down the line. There might also be some security implication - I can't think of specifics right now, but something in my mind tells me it seems risky
a
yep i m doing all these in test projects so np in sharing
i am developing discord bot in which bot will create new table for every new server it join (it will depend on size if it's member count is greater than 100i will create table so as to perform queries fast and if it's lesser than that then i will insert data ni exesting table as combined
s
Ah, gotcha. That's a good use case. There's probably other ways of doing it, but that's a good way.
a
yep i did something similar in firebase too so yeah
how do i share
s
Just post it in here
a
i m new so i don't know what do i post ? columns ? or link to the project and how will you access it
s
No, just post the query you've written
a
Copy code
sql
CREATE TABLE ID_OF_GUILD(
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  inserted_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  data jsonb,
  name text
);
it's template only
but i need it something lik ethis only
just i need the table name to be guild id
s
No worries - it might take me a while to get it ready, but I'll post here once I've figured it out 🙂
a
yep, thank you so much i was stuck n this from past 3 hrs
😄
s
Copy code
sql
-- If you wanted to create the table with the name 'ID_OF_GUILD'
CREATE OR REPLACE FUNCTION public.create_table()
RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
    CREATE TABLE ID_OF_GUILD(
      id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      inserted_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
      updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
      data jsonb,
      name text
    );
END;
$$;

-- SQL usage
SELECT * FROM create_table()


-- If you want to create the table with a custom name
CREATE OR REPLACE FUNCTION public.create_table_with_name(in_table_name text)
RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE FORMAT('
        CREATE TABLE IF NOT EXISTS %I (
          id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
          inserted_at timestamp with time zone DEFAULT timezone(''utc''::text, now()) NOT NULL,
          updated_at timestamp with time zone DEFAULT timezone(''utc''::text, now()) NOT NULL,
          data jsonb,
          name text
        );', in_table_name
    );
END;
$$;

-- SQL usage
SELECT * FROM create_table_with_name('this_is_a_test_name');
In the first example, you'd call it like this from the Supabase-js library: 
rpc("create_table")
In the second example: 
rpc("create_table_with_name", { in_table_name: "this_is_a_test_name" })
Alternatively, if you want to provide a default name:
Copy code
sql
CREATE OR REPLACE FUNCTION public.create_table_with_default_name(in_table_name text DEFAULT 'ID_OF_GUILD')
RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE FORMAT('
        CREATE TABLE IF NOT EXISTS %I (
          id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
          inserted_at timestamp with time zone DEFAULT timezone(''utc''::text, now()) NOT NULL,
          updated_at timestamp with time zone DEFAULT timezone(''utc''::text, now()) NOT NULL,
          data jsonb,
          name text
        );', in_table_name
    );
END;
$$;

-- SQL usage
SELECT * FROM create_table_with_default_name();
-- OR
SELECT * FROM create_table_with_default_name('this_is_a_test_name_2');
a
woah, man thank you i would ask more doubt will this thread be closed ?
s
As long as people are replying, the thread will remain active
a
alr i just got some time and i tested it and it's working absolutely perfectly
but the prob is it's only working in editor
not using supbase-js RPC
what i have done is
Copy code
sql
CREATE OR REPLACE FUNCTION public.create_table_with_name(in_table_name text)
RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE FORMAT('
        CREATE TABLE IF NOT EXISTS %I (
          id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
          inserted_at timestamp with time zone DEFAULT timezone(''utc''::text, now()) NOT NULL,
          updated_at timestamp with time zone DEFAULT timezone(''utc''::text, now()) NOT NULL,
          data jsonb,
          name text
        );', in_table_name
    );
END;
$$;
stored in a new query named as create_table
and i am calling rpc by the function name
rpc('create_table_with_name')
something like this
and passing params
but it's not working
what are you kidding wtf
it worked
i m so sorry lol
absolutely amazing
thank you again
PreviousNext
Powered by