Supabase #sql

burggraf

08/07/2021, 11:35 PM

I wrote up an article from my hackathon entry which explains how to solve a pretty difficult SQL problem: https://github.com/burggraf/dead-or-alive/blob/main/SQL_Challenge.md (sorry this is a cross-post from the hackathon channel but I thought it belonged here).

Subh

08/08/2021, 3:02 PM

Hey team, I am not able to figure out what's wrong on this one. Need another pair of eye 🙏 I got

syntax error at or near "::". null

Copy code

CREATE
OR REPLACE FUNCTION public.group_by_temp(col_name text DEFAULT 'country' :: text, project_id uuid :: uuid, date_range text :: text) RETURNS SETOF group_by_col_dummy LANGUAGE plpgsql STABLE AS $ function $ BEGIN RETURN QUERY EXECUTE format(
  'select  %I as col, count(*) from session where project_id = %s, %s::daterange @> created_at group by %I',
  col_name,
  project_id,
  date_range
  col_name,
);
END $ function $

Scott P

08/08/2021, 6:16 PM

col_name text DEFAULT 'country' :: text, project_id uuid :: uuid, date_range text :: text

should be

col_name text DEFAULT 'country', project_id uuid, date_range text

::

is used for coercing a value to a specific type and shouldn't be used for function args. For example, if you had a really long number (e.g. 20210808123456789012345678901234567890), you could use

:: TEXT

to force it to be

text

type instead of

int

type. Use cases could include working with JS, where you want to display the full value but the language itself might not be able to understand that it's a number.

Subh

08/08/2021, 6:18 PM

Thank you so much ❤️

Copy code

CREATE OR REPLACE FUNCTION public.group_by_col3(_colname text, _projectid text, _daterange text)
 RETURNS SETOF group_by_col_dummy
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN 
    RETURN QUERY EXECUTE format('select  %I as col, count(*) from session where project_id = ''%s'' AND tstzrange(''%s'') @> created_at  group by %I', _colname, _projectid, _daterange, _colname); 
END 
$function$

I ended up writing this.

Subh

08/08/2021, 6:19 PM

Which is pretty much what you are suggesting

Ilko Kacharov

08/09/2021, 8:56 PM

Hey there, has anyone tried to integrate an external db versioning with a tool like sqitch or flyway ?

dailylurker

08/11/2021, 8:47 AM

hi all I am getting the error below after creating a policy on my table, the policy is --> columnname in(select samecolumnname from table where user=uid()) the idea is to only select rows for the users that are in the same company as the one logged in "infinite recursion detected in policy for relation \"user_profile\""

burggraf

08/11/2021, 12:26 PM

Recursion in RLS Policy

dailylurker

08/12/2021, 12:25 PM

when we insert a new record and RLS is enabled does it trigger policies other than update? I am getting RLS violation when I try to create a new row and my only policy right now besides update is read

copple

08/12/2021, 2:14 PM

You might also need a

select

policy - see the note here: https://supabase.io/docs/reference/javascript/insert#notes

burggraf

08/12/2021, 2:28 PM

RLS Policies

LeDragunov

08/12/2021, 5:34 PM

Hello, -which one these is better way to store data? -can data in json later be used in count and summation?

Mihai Andrei

08/12/2021, 5:39 PM

Are there going to be

Mihai Andrei

08/12/2021, 5:39 PM

Only those 3 meta keys?

LeDragunov

08/12/2021, 5:44 PM

no, more

LeDragunov

08/12/2021, 5:45 PM

i might add attributes too like if the place has indoor pool, beach front, parking, etc.

Scott P

08/12/2021, 5:48 PM

Columns vs JSON

Mike92988

08/12/2021, 8:53 PM

i have a quick question if anyone is up for it: in my table i have a "userType" column and they will either be clients or admins, do you know if it would be possible to write a policy to allow only users who are "admins" to edit other users?

burggraf

08/12/2021, 11:59 PM

RLS based on userType

user

08/13/2021, 2:24 PM

Hi guys. Is it possible to export the raw SQL from my database? I started it with the table editor and can't seem to find a way to export it...

florian-lefebvre

08/13/2021, 3:33 PM

There is an open source project: https://supabase-schema.vercel.app/

florian-lefebvre

08/13/2021, 3:33 PM

Note that the sql schema generation is not 100% accurate

lawrencecchen

08/14/2021, 7:34 AM

you can also inspect the ddl using a tool like dbeaver (https://dbeaver.io/), which is very accurate

user

08/14/2021, 9:15 AM

Thanks for that!

user

08/14/2021, 9:17 AM

Another thing I wanted to ask; is my database structure any good? I know it's a vague question but I'm not used to relational databases... Below is the SQL. It's a chat app with users, channels (unique channels for group or 1-to-1 messaging) and messages within the channels

user

08/14/2021, 9:17 AM

Copy code

create type public.user_status as enum ('ONLINE', 'OFFLINE');
create type public.message_type as enum ('TEXT', 'IMAGE', 'VIDEO');

create table channels (
  id uuid default uuid_generate_v4() primary key
);

create table messages (
  id uuid default uuid_generate_v4() primary key,
  content text not null,
  type public.message_type not null,
  channelId uuid references channels (id),
  sentAt timestamp default now() not null
);

create table users (
  id uuid default uuid_generate_v4() primary key,
  name text not null,
  phoneNumber text not null,
  status public.user_status not null,
  image text not null,
  bio text
);

create table members (
  id uuid default uuid_generate_v4() primary key,
  userId uuid references users (id),
  channelId uuid references channels (id)
);

user

08/14/2021, 9:18 AM

as Supabase doesn't support

many-to-many

relationships, I've made a

members

table to join them together

Mihai Andrei

08/14/2021, 9:57 AM

It looks good for me

florian-lefebvre

08/14/2021, 10:21 AM

I have nested categories and for a category, I want to get one row per parent like: for id 3:

Copy code

text
[id: 3, title: Category3, parentId: 2],
[id: 2, title: Category2, parentId: 1],
[id: 1, title: Category1, parentId: null]

I found that postgres has recursive views so I found a tutorial and I have:

Copy code

sql
drop view if exists category_path;

create recursive view category_path (id, path) as
select
  id,
  title as path
from
  categories
where
  "parentId" is null
union all
select
  c.id,
  (cp.path || ' > ' || c.title) as path
from
  categories c
  join category_path cp on c."parentId" = cp.id;

Doing

select * from category_path where id = 3;

returns

[id:3, path:Development > Back-end]

. Here is my

categories

table:

florian-lefebvre

08/14/2021, 1:12 PM

Okay I got it:

Copy code

sql
drop table if exists resources;
drop table if exists categories;

create table categories (
  id bigint generated by default as identity,
  title varchar not null,
  parent_id bigint references categories (id),

  primary key (id),
  unique (title)
);

drop function if exists public.category_hierarchy;

create function public.category_hierarchy(id_param int)
returns table (
  id bigint,
  title varchar,
  parent_id bigint
)
as $$
begin
  return query with recursive parents
  as (
    select
      c1.id,
      c1.title,
      c1.parent_id
    from categories c1
    where c1.id = id_param
    union
      select
        c2.id,
        c2.title,
        c2.parent_id
      from categories c2
      inner join parents p on p.parent_id = c2.id
  ) select * from parents;
end;
$$ language plpgsql security definer;

and then

Copy code

sql
select * from category_hierarchy(5);

returns