yeah, I guess it depends on the type of the valid_until field

you'd have to use the correct JS type - don't remember how they map to PostgresSQL data/times

anyway, just wanted to mention it

Is it possible to pass a variable to a view? Or that requires a plpgsql function?

it's possible to add additional where clause to a view (just like a regular table) so you can add additional filtering on top of what the view already provides. Other than that, you cannot pass a parameter, you'd need a function for that

Hey this must be a common question, but is anyone getting "An error occurred with your deployment" in the sql execution screen? I think it happens for long-running queries

Hi, I want to filter a set of records where a specific column is an array of JSON. The following query:

SELECT 
  s.*,
  p
FROM service s
CROSS JOIN LATERAL json_array_elements(pets) p
WHERE p->>'type' == 'dog'

returns an error:

function json_array_elements(json[]) does not exist

Any idea? The column is a json[] datatype and the data instead is

[
  {
    "type": "cat",
    "price": 10
  },
  {
    "type": "dog",
    "price": 20
  }
]

I've read the official documentation about JSON data https://supabase.io/docs/guides/database/json - but there is no mention of an array of objects.

jaon[]

I have 3 tables (sql) Person, Teacher and Student is it one to one relation when the primary key of Person table is also the primary key of student table and that primary key is not in any teacher row?

Not 100% sure what you're trying to do, but you probably want a dedicated

person_id

foreign key column on both your

teacher

and

student

tables. You can then enforce a 1:1 relationship between

student

and

person

by having a unique constraint on the

person_id

column in the

student

table. However, depending on what you're trying to do, you might no even need a person table. Something like

select id, name from student union all select id, name from teacher

will give you all the "people".

Hey everyone, I'm working with the beta trigger functions and having an issue with the .rpc (stored procedures). I’m hitting an error stack limit depth exceed error. I’m running this on insert, so it shouldn’t be cycling through with update. For additional details - we’re building a booking app. When we get a reservation start_date and end_date. Right now we’re only trying to update the ‘nights’ (delta between start & end). Eventually we need to calculate the cost and other info, but starting with this one number to start.

Does anyone have an example trigger which updates a newly inserted record using some of the new record data? cc: @User

What would be the best way to model recurring events like in a todo list (e.g. clean the kitchen every week) in Postgres?

How about cron. That way automated tasks can be automated easily with something like pg_cron / pg timetable.

I use FlyWay for DB migrations, which allows a particular migration to be re-run everytime the contents of the migration file changes. This is quite handy for logic (e.g. views, functions, triggers) since you don't need to create a new file for every change. This also works quite well with PostgreSQL's "CREATE OR REPLACE" statements.

I have defined a very simple trigger on the auth.users table - all it does is insert a record into a logging table.

I have no idea why this rpc doesn't work. It returns an empty array, while the sql equivalent returns the expected table.

create or replace function fetch_slim_profile (
   userid uuid
) 
returns table (return_id bigint, return_avatar varchar, return_fullname varchar, return_app_received boolean, return_app_accepted boolean, return_conversation_started boolean, return_search_tracking boolean) 
language plpgsql
as $$
begin
return query select profiles.id, avatar, fullname, app_received, app_accepted, conversation_started, search_tracking from profiles inner join 
user_settings on profiles.user_id = user_settings.user_id where profiles.user_id = userid;
end; $$

Many times these things are related to RLS and whether the rpc caller has ability to access tables. I'm not looking at specifics in your code, but adding "security definer" allows it to bypass RLS if needed (which sql call would). Once again not looking at specifics if that even matters in your code.

dang

That might be it, maybe not. Thanks for the insight. I'll look into it.

@User adding security definer worked. I would have literally never attempted that.

Thank you.

Well as long is it is OK to bypass RLS on the call.....

ha! indeed

Does anyone know why the PostgreSQL instance is exposed on 2 ports: 6543 and 5432 (the former is listed in Databas -> Connection Pooling -> Connection Info, the latter is listed in Settings -> Database -> Connection Info)?

Does anyone know the exact meaning of the "Extra search path" setting in API settings of the dashboard?

Hi guys! can you help me in this step? https://github.com/supabase/supabase/discussions/4042

This is my SQL function

SQL
create or replace function change_user_password(current_plain_password varchar, new_plain_password varchar)
returns json
language plpgsql
security definer
SET search_path = extensions
as $$
DECLARE
_uid uuid;
BEGIN
  -- First of all check the new password rules
  -- not empty
  IF (new_plain_password = '') IS NOT FALSE THEN
    RAISE EXCEPTION 'change_user_password refuse'
    USING DETAIL = 'new password is empty';
  -- minimum 6 chars
  ELSIF char_length(new_plain_password) < 6 THEN
    RAISE EXCEPTION 'change_user_password refuse'
    USING DETAIL = 'it must be at least 6 characters in length';
  END IF;
  
  -- Get user by his current auth.uid and current password
  SELECT id INTO _uid
  FROM auth.users
  WHERE id = auth.uid()
  AND encrypted_password =
  crypt(current_plain_password::text, auth.users.encrypted_password);

  -- Check the currect password
  IF NOT FOUND THEN
    RAISE EXCEPTION 'change_user_password refuse'
    USING DETAIL = 'incorrect password';
  END IF;

  -- Then set the new password
  UPDATE auth.users SET 
  encrypted_password =
  crypt(new_plain_password, gen_salt('bf'))
  WHERE id = auth.uid();
  
  RETURN '{"data":true}';
END;
$$

the code is works fine in Supabase SQL editor, but when i call it as an RPC i got this error message

{ "hint": null, "message": "invalid salt", "code": "22023", "details": null}

Can one get the header information in an sql function in Supabase? I'd like to record some information on a request but can't find anything for doing that.

EG: getting the user_agent header etc