Is there a way to transpose a table like this?

I don’t know much about it. I think the search term you are looking for is pivot. See https://www.postgresql.org/docs/current/tablefunc.html

Yes I found this too but I could not figure out how to make it work with an already complicated query. I was hoping for some other options but google was not very helpful. I think thats the only option for this.

random SQL-ish related question, when you all

supabase db remote commit

and it generates a

{date}_remote_migration.sql

, do you all rename the

remote_migration

part of the name? Kind of annoying that it doesn't let you give it a name in CLI like the local equivalent

Lets say I have this database function that returns a trigger

sql
CREATE OR REPLACE FUNCTION update_row ()
  RETURNS trigger AS $$
    BEGIN
    
        NEW."random_field" = 'text';

        RETURN NEW;
    END;
  $$ LANGUAGE plpgsql security definer;

And I create a trigger for

ON INSERT

and make it run

BEFORE INSERT

. I know that the new row will have 'text' in its

random_field

. But if the trigger is made to run

AFTER INSERT

, would the new row still get the updated

random_field

?

I don't think it gets updated. But why not test it yourself.

I will try it out and see if it updates.

Can anyone please have a look at this thread in #843999948717555735 https://discord.com/channels/839993398554656828/969340655514042409 I am really stuck and dont know what to do to resolve this issue.

Hey im working in PLpgSQL and i want to work with IN clause which takes value from an array of strings i pass from my server but i cant get it to work, like idk how to pickup that array in plpgsql and put it in the IN clause

Array use = ANY(

sql
CREATE OR REPLACE FUNCTION get_account_balances (account_names text[])
    RETURNS JSON AS $$
    DECLARE
        t_row db_transactions;
        account TEXT;
        balances TEXT[];
    BEGIN
        FOREACH account IN ARRAY account_names
        LOOP
            SELECT *
            INTO t_row
            FROM public.db_transactions
            WHERE (deb_acc = account 
                OR cred_acc = account)
            ORDER BY t_date DESC, updated_at DESC
            FETCH FIRST ROW ONLY;

            RAISE LOG 'Row with the account % is %', account, t_row;

            IF t_row.deb_acc=account THEN
                balances = array_cat(balances, ARRAY[account, COALESCE(t_row.deb_acc_bal,0)::TEXT]);
            ELSE
                balances = array_cat(balances, ARRAY[account, COALESCE(t_row.cred_acc_bal,0)::TEXT]);
            END IF;

            RAISE LOG 'Balances so far: %', balances;
        END LOOP;
        RAISE LOG 'Balances length: %', array_length(balances, 1);
        RETURN json_build_object(balances);
    END;
    $$ LANGUAGE plpgsql security definer;

I am getting this error in the logs:

argument list must have even number of elements

but I know for a fact that cant be true as I am only adding two values to the

balances

array with each loop where one should be the key and the other should be the value. What am I doing wrong here?

I think that what is happening is that the

json_build_object

function is taking the

balances

array as a single element. That is why the argument is odd. So is there a way to make it so the

json_build_object

takes the internal elements of the

balances

array?

json_build_object takes a list parameter, json_object takes a text array with pairs of items or array of two item arrays to build key:value object.

@garyaustin So would

json_object

create something like this:

{'a':'b'}

? If so, I could "add" some to an array like

balances

here like this:`[{'a':'b'},{'c':'d'},{'e':'f'}]`. How would I go from this to this -->

{'a':'b','c':'d','e':'f'}

which is a single json object.

json build object takes a list parameter

hey i am working with a sql query which works on filter which takes an array for filter, but my issue is that there might be a case when that filter array is not provided in that case i dont want to use that WHEN condition which filters using that array, so can i use some sort of if else in WHEN where if the provided array is empty i dont use that condition?

hey i am working with a sql query which

i m adapting these policies from a

Hey guys, I've been running into a problem with policies and I can't figure it out on my own. I have a public table called User that has an id uuid column that references auth.uid(), and a role varchar column. I have another public table called Companies. I'm trying to make it so that only User with role = 'staff' can insert on table Companies. Here's what I come up so far:

pgsql
CREATE OR REPLACE FUNCTION get_user_role(uid uuid) RETURNS VARCHAR AS $$ 
DECLARE user_role varchar(255);
BEGIN
  SELECT role INTO user_role FROM public.user WHERE id = uid;
  RETURN user_role;
END;
$$ LANGUAGE plpgsql

And here's the policy:

pgsql
ALTER POLICY "Enable insert staff users only" ON public.company WITH CHECK (get_user_role(uid()) = 'staff');

I'm receiving row lever security violation when I'm trying to insert as a staff user.

I am implementing a simple function in sql like this:

sql
CREATE OR REPLACE FUNCTION get_transactions (start_date TIMESTAMP, end_date TIMESTAMP, t_cat TEXT, t_account TEXT)
    RETURNS db_transactions AS $$
    DECLARE
        
    BEGIN
        RETURN QUERY
        SELECT *
        FROM PUBLIC.db_transactions
        WHERE (t_date >= start_date OR t_date < end_date) AND category = t_cat AND (deb_acc = t_account OR cred_acc = t_account)
        ORDER BY t_date DESC, updated_at DESC;

    END;
    $$ LANGUAGE plpgsql SECURITY DEFINER;

This would show some rows from a table where the user can filter and search for rows depending on their criteria. By default, I want to show all transactions. Is there a way to make it so that if the arguments are lets say

NULL

or anyother placeholder string, the whole

WHERE

clause is ignored? Furthermore, it is also possible that the user may enter no dates but other arguments are entered. In that case, I also want relevant rows.

You can put a or statement Where (start_date is null or t_date >= start_date ) and ( end_date is null or etc etc

@tourdownunder Wouldn't that filter out where those fields are actually

NULL

? That is unlikely for my case but just to be safe, is that possible?

I just tried this method. It is working so far. There's some boilerplate but it does work.

Hello there! I'm currently trying to build a search bar, and I need to get data from supabase - I found out that I needed to do an SQL request : SELECT logo FROM assets WHERE to_tsvector(name) @@ to_tsquery(searchName+':*') I'm now trying to create a SQL function on supabase terminal to be able to call it with my node client as a RPC. However, I can't find any documentation about the syntax when creating a function on SQL. Anyone could help me? Thanks!

CREATE OR REPLACE FUNCTION public.search(searchName text) RETURNS [text, text] LANGUAGE 'plpgsql' AS $$ begin SELECT logo,name FROM assets WHERE to_tsvector(name) @@ to_tsquery(searchName+':*') return name,logo; end $$; Here is the monster I created.

tsvector function

Hi everyone, I need a bit of help. I have a couple tables ('characters', 'locations' etc) that store documents. I'm trying to create a timeline that allows people to add instances of a document to the timeline. For this I figured I'd create a 'document_instances' table to join an instance in a timeline to the document itself. The issue is that because I have multiple tables, I end up needing to construct a join based on the table name in a column. So you can see that document_id is the row I need to join and document_type is the table that the row resides in. Now I've heard this is a bad idea, but I don't really know how else to construct this functionality without this document_instance table.

Table JOINS

IS it possible for someone to meet with me over zoom to go over my project? I'm having trouble with row level security rules making it impossible for me to select data even though hte data is there. Not sure why I thikn the supabase client is logged in OK, my rule is very ysimple just uid()=user_id

when a table column data type changes does its view also update?