hey guys, i am trying to get current income for this month in my app: i have two tables where i have

project_time_entries

with columns

duration

in minutes, and table `project_members`where i have column `cost_rate`which is hourly rate of employee. so what i want is to join those two tables, calculate all durations for this month and get income. this is what i trie and it returns me duplicated rows

create or replace function get_time_entries_income (
  start_date text, end_date text
) 
    returns table (
        duration integer,
        cost_rate integer
    ) 
    language plpgsql
as $$
begin
    return query 
        select pt.duration, p.cost_rate
        from project_time_entries pt
        inner join project_members p
        on pt.member_id = p.member_id
        where work_date between start_date and end_date;
end
$$;

Hi I am trying to create a spatial table

I have a question regarding rpc's. I've build a function that searches into 4 different tables. It returns a json object with 4 lists of results for each table. Does it make sense to return heterogeneous data in a function or make 4 separate requests out of them to keep it simple? The reason that I'm asking is because I came across https://github.com/nuxt-community/supabase-module/blob/main/demo/pages/tasks.vue#L14 where generics are used which could be difficult for rpc's returning heterogeneous data (right?)

would anybody be able to give me some help migrating my supabase DB schema over to GCP? I plan on using supabase for dev, and am having a tough time with the best way to just copy the db schema over to my GCP Database

would anybody be able to give me some

Hey i was wondering this following query:

WHERE U.id IN (SELECT ids from another table with some criteria)

Does this execute the SELECT id just once? Is it better then join? What about a query which matches id with some other value like

WHERE U.id = (SELECT id from another table with some criteria)

Does this run the SELECT id for every U.id in U for every row?

I don't have a performance answer on what gets "cached" in RLS joins/selects. Using a postgres stable function is one thing I use when I know the data is the same for each row being check with the policy. https://www.postgresql.org/docs/current/xfunc-volatility.html

thanks

hey guys, i want to create a function that returns array of ids, tried something like that

CREATE OR REPLACE FUNCTION random_questions()
RETURNS int[] AS $$
  SELECT array[id] FROM questions ORDER BY random() LIMIT 10;
$$
LANGUAGE SQL;

and this returns an array only one id, what i am doing wrong? any ideas?

SELECT array[id] FROM questions ORDER BY random() LIMIT 10; this returns 10 id's as single elements of 10 arrays, but i need 1 array with 10ids

SELECT array_agg(id) FROM questions ORDER BY random() LIMIT 10;

did the trick, thanks!

Hello, how to sort and order using JSON column?

[
  {
    "date": [
      "2022-05-24",
      "2022-05-26"
    ]
  }
]

Here's the sample data that I have in my JSON column. It can have multiple dates

Delete Trigger not working '''-- Users Delete Function create or replace function public.handle_delete_user() returns trigger as $$ begin delete from public.users where id = new.id; return new; end; $$ language plpgsql security definer; -- END Users Delete Function -- -- Users Delete Trigger create trigger on_auth_user_deleted after delete on auth.users for each row execute procedure public.handle_delete_user(); -- END Users Update Trigger'''

Can someone help me please.

My trigger on insert and update are working but on delete not. I cannot find any errors in the logs. The user from the auth table is deleted but the user from the public.users is not deleted.

Delete uses old not new for the existing data. New is null.

@garyaustin Thanks, that did the trick. I guess the Supabase guy don't delete much because there is almost nothing in the docs about deleting.

Alot of people use cascading deletes on their foreign keys, so when you delete the parent all children get deleted too.

I tried that too but when I make the id field of my public users a foreign key I cannot delete the auth.user anymore

@garyaustin From my app I delete the user in the auth.users so my guess is is that the id field of auth.users should be set as a foreign key but that is not allowed.

can i delete row from table without delete relations with it ?

If you mean you have a foreign key in table B pointing to the row in table A. Then you delete the row in table A, you don't want to use cascade delete to delete the row in table B? You have to do something with the foreign key, either delete the row or null the foreign key entry out. https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-foreign-key/ shows a way to null the foreign key instead of deleted the row.

auth.uid() not working inside database function I have a function like below:

create or replace function test_function()
  RETURNS text
  LANGUAGE plpgsql
  security definer set search_path = auth
as $$
  declare user_role text;
  begin

    select raw_user_meta_data->>'role' 
    from auth.users
    into user_role
    where id = auth.uid()
    return user_role;
end
$$;

When I call this function from within my js app it returns null

const {data, error} = await supabase.rpc('test_function')

When I replace auth.uid() with the id of an existing user it still returns null in my code but when I run

select * from test_function()

in Supabase Studio's SQL Editor I do get the role I assigned to my user Why is there a difference between calling a function from code and calling it from the SQL Editor? And why is auth.uid() not working? To be clear; there is an authenticated user in the code that I'm calling from.

auth uid not working inside database

Anyone able to help me with a query I'm trying to get working? I want to generate variable time series based on two values I have stored in my database. I can get it working when I hard code the values but I don't know how to make them come from a subquery. I'm a postgres noob so I feel like it's definitely me and should be possible. Here's my query with hard coded values

SELECT t.day::timestamp
FROM   generate_series(timestamp '2022-07-20 09:00:00.000000 +00:00'
                     , timestamp '2022-07-20 21:00:00.000000 +00:00'
                     , interval  '1 hour') AS t(day);

Today was my first time using sql because of supabase

How closely is the syntax related from the supabase to regular old SQL minus the JavaScripty part?

well, sql has multiple dialects, so depends on what you consider regular old SQL. For Supabase, it uses the Postgresql database so it uses the postgresql dialect. but it should be nearly the same.

incase you want to know, what is supported and whatnot -> https://www.postgresql.org/docs/current/sql-commands.html

Ok cool