im actually a frontend developer not a backend developer. so believe me, its really hard for me to work with postgreSQL and i searched a lot for my question but i couldnt find exactly what i want. i want to create a query in supabase that returns all rows of 3 tables. my goal is to search in these rows for search results base on users entered text in my app. so i want to return all rows from 3 tables in a function and a request. then by using the methods of supabase js library i can filter the rows. you can understand my point better with this code example:
create
or replace function search()
returns /* what type of data should i return? */
language sql as $$
/*
* select all rows of table artists -> select all rows of table musics -> select all rows of
* table playlists
*/
$$;
then i can do something like this in js:
let query = supabase.rpc('search').ilike('name', search_text)
so how can i do something like this? thanks for helping.
UPDATE here are the rows with some of theie columns that are in my 3 tables: note: the rows of my 3 tables are totally different from each other.
a row of table *musics*:
id | name | singer | listenTimes | link
1|without me|eminem|10000|www.test.com
a row of table *artists*:
id | name | monthlyListens | bio
1|eminem|123431|{bio infos}
a row of table *playlists*:
id | name | musicsId | musics | cover
1|your favorite playlist|[1,2,3]|3|www.test.com/cover.png
base on jiri baums answer, i want to create a function that has an argument. what this function does is to check the columns of rows from 3 tables to check if the argument is something like the value of a column and returns the rows that at least have a column like the value of argument. for example: the function has an argument with the value of emvnem. the rows 1 and 2 in the exampla above will be returned. because emvnem is like eminem. if its is exactly like eminem, the returned rows are row 1 and 2. so how can i implement something like this?
As a general rule, this would be a poor design:
In terms of efficiency, it's best to push filtering as close as possible to the data; PostgreSQL has ilike functionality built in, indeed the queries in supabase are based on SQL syntax. See the PostgreSQL docs for LIKE and ILIKE
SELECT *
FROM table_name
WHERE name ILIKE 'search text'
With any real amount of data, sending everything to the front-end for filtering will be prohibitive.
In terms of access to data, if you send the whole table to the front end, the user will be able to copy it, save it, do other things with it; in most cases, you probably prefer to avoid that.
If you nevertheless want to do this, the operator you're looking for is UNION; the PostgreSQL docs have an example of doing pretty much exactly this:
SELECT distributors.name
FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
FROM actors
WHERE actors.name LIKE 'W%';