https://supabase.com/ logo
Join Discord
Powered by
# sql
  • t
    so the problem of SQL functions into RSL is like the table path, giving the absolute path instead of table name only would be fix the issue
  • t
    I will try in a couple of minutes
  • t
    work at 100%
  • p
    @User interesting - that seems to mostly fix the issue. However I am now seeing that row-level subscriptions are not being triggered (if I have a catch-all 
    *
    subscription, they do trigger correctly).
  • p
  • c
    Search logs via query
  • c
    Hey ya'll. Fairly new to supabase, but i'm really digging it so far. Small question for setting up policies for my 
    users
    table. I've created a custom auth flow that makes use of ethereum wallet signatures. The user's 
    id
    is basically their wallet address (a string) & is stored in JWT in the 
    sub
    claim. When creating my policy, i'm writing it like so 
    auth.uid() = address
    but I get an error on the supabase UI that looks like this
    g
    • 2
    • 1
  • c
    Seems like 
    auth.uid()
    is expecting a different type of data. How can I get around this to accomplish what I'm doing here? Any advice is much appreciated. Thanks!
  • g
    uid with a string
  • y
    Hello everybody, is it possible to trigger for eg an http request on a policy check fail ?
    c
    • 2
    • 15
  • o
    Hello, I found this PostgreSQL function that uses regex to extract hashtags from a string. It works well, but I can't be sure if it's safe to use with the 
    execute
    and 
    captures
    ? Would I need to escape something?
    Copy code
    SQL
CREATE FUNCTION parse_tokens(content text, prefix text)  
  RETURNS text[] AS $$
    DECLARE
      regex text;
      matches text;
      subquery text;
      captures text;
      tokens text[];
    BEGIN
      regex := prefix || '(\S+)';
      matches := 'regexp_matches($1, $2, $3) as captures';
      subquery := '(SELECT ' || matches || ' ORDER BY captures) as matches';
      captures := 'array_agg(matches.captures[1])';

      EXECUTE 'SELECT ' || captures || ' FROM ' || subquery
      INTO tokens
      USING LOWER(content), regex, 'g';

      IF tokens IS NULL THEN
        tokens = '{}';
      END IF;

      RETURN tokens;
    END;
  $$ LANGUAGE plpgsql STABLE;
    k
    • 2
    • 4
  • j
    So, I want to have a local copy of all my plpgsql functions, in case they get lost or if I want to re-create the DB locally. Right now I am copying them manually over to supabase, but is there an API to push them to supabase, so I can automate this? Thanks!
    s
    o
    • 3
    • 7
  • k
    Extracting hashtags
  • b
    how to you create a RLS rule that allows count(*) group by primary_key without allowing access to the data in the table?
    s
    s
    • 3
    • 37
  • b
    is there any problem with making user 'postgres' a superuser if I have a strong password on it?
  • t
    guys not a supabase related question, but... somebody of u have a postgres function to calc distance (km) betweend 2 coordinates?
    s
    s
    • 3
    • 6
  • s
    PostGIS - Calculate distance between coords
  • b
    When doing db migrations with https://supabase.com/docs/guides/database#migrating-between-projects - don't we need to worry about the potential for changes in the 'Supabase originated' schemas (like auth, realtime, storage, pgbouncer, extensions)? Say I have a project based on PostgreSQL 12 / 2020 and migrate into a PosgreSQL 15 / 2022 project, won't there have potentially been changes/improvements to the auth/realtime/storage schemas since that last 2 years of Supabase-side development that will now get overwritten by the old 2020 schema?
  • c
    Does anyone know why new functions in the public schema have a permission for "PUBLIC" in addition to the "anon", "authenticated", "service_role" and "postgres" roles? I understand why those roles have execute permissions (they are granted permission by default as specified here: https://github.com/supabase/supabase/blob/76e1254a91cd5c6b1eb80d3faf19cfd6a3735a95/docker/volumes/db/init/00-initial-schema.sql), however, I do NOT understand why PUBLIC also gets these permissions (as far as I know, PUBLIC is a reserved keyword meaning 'all roles')
    g
    • 2
    • 4
  • c
    I am trying to use pgTAP using the xUnit-style way of writing tests (i.e. write test functions as stored procedures and then invoke them with SELECT * from runtests('mytestschema'::name)
    t
    • 2
    • 13
  • k
    Hey guys, i am trying to delete things that are related to some reference. For example I have post and that post have comments, if I as an owner delete that post from my profile, I want to delete all comments which are related to that post id. Is there any solution for it? I researched and found something like cascade deleting or something like that. I am not the expert in sql. 🙂
  • b
    Copy code
    CREATE TABLE post (
    post_id integer PRIMARY KEY,
    ...
);

CREATE TABLE post_comment (
    post_id integer REFERENCES post ON DELETE CASCADE,
     ...
);
  • b
    @User something like above will do what you want, see near the bottom of this page for more details: https://www.postgresql.org/docs/14/ddl-constraints.html#DDL-CONSTRAINTS-FK
  • k
    @User thanks man, is there a possibility to update current database table?
  • k
    or i need to create a new one via sql editor in supabase?
  • k
    You can do this with SQL, you'll need to remove the Foreign Key constraint with 
    ALTER TABLE DROP CONSTRAINT constaint_name
    , and then add it back with 
    ALTER TABLE post_comment ADD FOREIGN KEY (post_id) REFERENCES post (post_id) ON DELETE CASCADE
  • k
    (bonus points for doing both in one transaction)
  • k
    ALTER TABLE is described here: https://www.postgresql.org/docs/14/sql-altertable.html (PostgreSQL reference documentation is very good, use it ;-))
  • k
    Hey guys whats wrong with this function
    Copy code
    create or replace function get_feed_posts() returns setof feed
as $func$ 

SELECT *
, (SELECT COUNT(*) FROM feed_activities WHERE feed_ref=feed.id and type = 'like') as LIKE_COUNT
, (SELECT COUNT(*) FROM feed_activities WHERE feed_ref=feed.id and type = 'comment') as COMMENT_COUNT
FROM feed

$func$
language sql;
    c
    • 2
    • 21
  • k
    i am getting this error in sql editor
1...303132...52Latest