-- inserts a row into public.users
create OR REPLACE function public.handle_new_user() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.profiles (id, raw_user_meta_data)
  values (new.id, new.raw_user_meta_data);
  return new;
end;
$$;

-- trigger the function every time a user is created
DROP TRIGGER IF EXISTS on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

await supabase.auth.update({
      data: { account_deleted: new Date() },
    });

transactions

journals

journals

transaction_id

is_credit=true

is_credit=false

transactions

journals

sql
CREATE TABLE IF NOT EXISTS public.accounts (
  id uuid NOT NULL DEFAULT uuid_generate_v4(),
  created_at timestamp with time zone DEFAULT now(),
  name text COLLATE pg_catalog."default" NOT NULL,
  CONSTRAINT accounts_pkey PRIMARY KEY (id),
);


CREATE TABLE transactions (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
  created_at timestamp with time zone DEFAULT now(),
  transaction_date date not null default current_date,
  description text NOT NULL,
  CONSTRAINT transactions_pkey PRIMARY KEY (id),
);

CREATE TABLE journals(
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
  transaction_id bigint NOT NULL,
  account_id uuid NOT NULL,
  amount NUMERIC(20, 2) NOT NULL,
  is_credit boolean NOT NULL,
  CONSTRAINT journals_pkey PRIMARY KEY (id),
  CONSTRAINT journals_transaction_id_fkey FOREIGN KEY (transaction_id)
    REFERENCES public.transactions (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
  CONSTRAINT journals_account_id_fkey FOREIGN KEY (account_id)
    REFERENCES public.accounts (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
);

syntax error at or near "insert"

sql
create function commands_inc (x int, row_id text) 
returns void as
$$
  insert into command_logs (id, commands) values (row_id, x) on conflict do update
$$ 
language plpgsql volatile;

create or replace function latestOilFee()
return array
IS
Begin
select * from oil_tracker order by ts desc fetch first 1 row only;
End;

SELECT * FROM users

To insert:
{ "a": 1, "b": 2 }

Already there:
{ "a": 2, "b": 3, "c": 1}

What I want:
{ "a": 3, "b": 5, "c": 1 }

CREATE OR REPLACE FUNCTION public.set_username(new_username text)
    RETURNS public.profile
    LANGUAGE 'plpgsql'
    SECURITY INVOKER
    SET SEARCH_PATH = public, auth
AS
$$
declare
    updatedProfile public.profile;
begin
    insert into public.profile(id, username)
    select auth.uid(), new_username
    returning * into updatedProfile;

    return updatedProfile;
end
$$;

security invoker

search_path

SECURITY DEFINER

(if 
   auth.role() = 'authenticated'
   and 
   is_draft = false
 then
   true;
elsif
   auth.uid() = user_id 
then
   true;
else
  false;
end if;
)

sql
CREATE FUNCTION node_cte(node_ids int[], org_id uuid)
...
WHERE table.id IN node_ids

sql
create function increment (row_id int) 
returns void as
$$
  update "Song" 
  set listening = listening + 1
  where id = row_id;
$$ 
language sql volatile;

EXECUTE

EXECUTE format('SELECT * from result where id=%s', '"42"');

Message

CREATE TABLE IF NOT EXISTS public.message
(
    id uuid NOT NULL,
    sender_user_id uuid NOT NULL,
    recipient_user_id uuid NOT NULL,
    conversation_id uuid NOT NULL,
    content text COLLATE pg_catalog."default" NOT NULL,
    read boolean DEFAULT false,
    message_warning boolean DEFAULT false,
    CONSTRAINT message_pkey PRIMARY KEY (id),
    CONSTRAINT message_conversation_id_fkey FOREIGN KEY (conversation_id)
        REFERENCES public.conversation (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT message_sender_user_id_fkey FOREIGN KEY (sender_user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
)

"custom" Column Level Security

RLS Policy

content

read

RLS Policy

db function

is identity

SELECT PG_GET_SERIAL_SEQUENCE('mytable', 'id');

relation "mytable" does not exist