I am trying to add this in an RPC function in Supabase but I'm not sure why it's only returning the first column.
SELECT
column_name,
data_type
FROM
information_schema.columns
WHERE
table_name = 'frappe';
It returns this:
{ column_name: 'id', data_type: 'bigint' }
How can I get all the results? Is it possible to call this function using the Javascript Libraries?
This is how you can get a list of columns and their types from Postgres using a Supabase function. To achieve this, you can get the data from Postgres' information_schema.columns table.
CREATE OR REPLACE FUNCTION get_types(tname text)
RETURNS TABLE ( column_name text,data_type text ) AS
$BODY$
BEGIN
RETURN QUERY EXECUTE format($$SELECT column_name::text,data_type::text FROM information_schema.columns WHERE table_name ='$$|| '%I' ||$$';$$,tname);
END;
$BODY$
LANGUAGE plpgsql;
Please note that this is a more advanced function and you should deploy it using the SQL Editor or PSQL and not using the Supabase UI (return types of table / setof is not available there). Here are the results from calling this function in SQL:
Since this is a Postgres function, you can call it using Supabase Javascript API for RPC:
const { data, error } = await supabase
.rpc('get_types', {tname: 'frappe'})
[
{ column_name: 'cnt', data_type: 'bigint' },
{ column_name: 'item', data_type: 'text' },
{ column_name: 'daytime', data_type: 'text' },
{ column_name: 'weekday', data_type: 'text' },
{ column_name: 'isweekend', data_type: 'text' },
{ column_name: 'homework', data_type: 'text' },
{ column_name: 'cost', data_type: 'text' },
{ column_name: 'weather', data_type: 'text' },
{ column_name: 'country', data_type: 'text' },
{ column_name: 'user', data_type: 'text' },
{ column_name: 'city', data_type: 'text' }
]