I'm trying to write a function which simply updates a value in the database if called

sql
create or replace function foo(id uuid) returns boolean as $$
  begin
    update tablename set bar='test';
    return true;
  end
$$ language plpgsql;

This returns

true

but the table rows are not updated. When commenting out everything except the update query, it updates as expected. Why?

Can't tell if this is a bug... I'm trying to create a unique case insensitive column using citext. I've installed the extension, created the column, however when I try to make it unique I get "could not create unique index" - afaik this should be possible with citext

This happens using the gui or with the following

ALTER TABLE table_name
ADD CONSTRAINT column_name_unique UNIQUE (column_name);

Would love any help/ suggestions! Would rather not use lower and ilike queries

Do you have any data in the column? Specifically empty strings? Citext does work with unique constraint.

trying to create a fkey to auth.users.email_change_token_new. but getting an error--there is no unique constraint matching given keys for referenced table "users", this happens when Is Unique checkbox is either checked on unchecked, Any ideas?

The error is on auth.users column not being unique (not your link you are creating), and you really should not change the auth schema.

Those are unique columns, so you can have foreign links

not looking to change it, just looking to create a relation to my profiles table, like i did with auth.id, and auth.email

can i query the auth.users able directly then?

Pretty new to this; What is the equivalent of the .valid? method in Ruby for Postgres? My goal is to utilize the .rpc method to evaluate if a record exists and return a boolean

I have a tasks table, a projects table and a projects_tasks table. Pretty simple relationship stuff. However I have a need that I believe is outside my sql knowledge. I need a unique incrementing value (just a simple int) per project that goes up every time a task is added, but only relative to the previous task with the same project_id. So it could be like this...

id   task_id   project_id   project_task_number
------------------------------------------------
1    15        42           1
2    16        42           2
3    17        44           1
4    18        44           2
5    19        45           1
6    20        42           3
7    21        42           4

What is the best way to go about this?

count of tasks by project id

Does anybody know how to get this set up on Supabase? In the Supabase SQL editor, I'm getting an error when running this script Script I want to run: https://supabase-sql.vercel.app/youtube-like-short-id Error:

type "shortkey" does not exist

Likely because the

pg_shortkey

extension isn't installed in the database, meaning the types it generates don't exist. There's a thread for extension suggestions available at https://github.com/supabase/supabase/discussions/679

Oh ok I see. So this extension isn't available on supabase? How did the user who posted that script get it to work on supase?

Actually, I've just re-read this. Did you run the script at https://github.com/turbo/pg-shortkey/blob/master/pg-shortkey.sql (that's the source of the shortkey type)? It only requires the pg-crypto extension, which is already installed in Supabase (Reply in the thread so we can avoid cluttering up the main chat)

Hey, im with a question. Theres a function to get the current timestamp in miliseconds ? Like

'uuid_generate_v4()'

nicee it gives me a str like "1646335448040.042000". How can i convert it to a integer? im using

SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000;

Did you try googling "postgres text to int"?

its supose to work with ::integer but still give me a str thats why i ask

I've set up the following function which intends to set the

modified_by

to the current user's uuid. All the triggers are set correctly, but when i update a row from the Table Editor, nothing happens. Am I missing something?

-- Create function for updating the modified_by column
CREATE OR REPLACE FUNCTION sync_modified_by() 
RETURNS trigger AS 
$$
BEGIN
  NEW.modified_by := auth.uid();

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Could be a couple of things, but just glancing.... auth.uid() would be null for table editor I would think. 2nd, the table editor, unless a new fix has rolled in already, updates the entire row no matter which column you change. This caused problems with generated columns, but they would error. I would think a trigger could override any data from Table Editor though on that column.

So you’re thinking that once I’ve got my UI hooked up with legit users, it’ll work alright? It’s just returning null cause of the Table Editor being the entry point to the update?

If you are getting null, that is probably it. auth.uid only works with a jwt token from the api.

Super confused by this. I have the following function. When I run the query as just a query, it returns all of my data just fine. When I try to run it in this function, it throws an error Query:

CREATE OR REPLACE FUNCTION get_folder_tree(sk varchar)
RETURNS TABLE (id int, name text, parent_id int, shortkey varchar)
AS $$
BEGIN
  RETURN QUERY
    WITH RECURSIVE 
      -- starting node(s)
      starting (id, name, parent_id, shortkey) AS
      (
        SELECT t.id, t.name, t.parent_id, t.shortkey
        FROM folders AS t
        WHERE t.shortkey = sk
      ),
      descendants (id, name, parent_id, shortkey) AS
      (
        SELECT s.id, s.name, s.parent_id, s.shortkey
        FROM starting AS s
        UNION ALL
        SELECT t.id, t.name, t.parent_id, t.shortkey
        FROM folders AS t JOIN descendants AS d ON t.parent_id = d.id
      ),
      ancestors (id, name, parent_id, shortkey) AS
      (
        SELECT t.id, t.name, t.parent_id, t.shortkey
        FROM folders AS t 
        WHERE t.id IN (SELECT parent_id FROM starting)
        UNION ALL
        SELECT t.id, t.name, t.parent_id, t.shortkey
        FROM folders AS t JOIN ancestors AS a ON t.id = a.parent_id
      )
    TABLE ancestors
    UNION ALL
    TABLE descendants ;

END;
$$ LANGUAGE plpgsql;

Error:

column reference "parent_id" is ambiguous

Don't know if any SQL guru's will come along soon and not sure why function would matter, but I'd look at WHERE t.id IN (SELECT parent_id FROM starting) . As the error is it does not know which parent_id you are referring to. You must be awakingpanda to write a function like that. Good luck.

That could be it. Try

sql
(SELECT s.parent_id FROM starting s)

Crazy SQL Function