我希望创建一个 RPC 来获取列的不同值,但我将在多个表中执行此操作,并且我不想为每个列和每个表一遍又一遍地编写此函数。我有以下 SQL 语句可以满足我的需要:
SELECT owner
FROM customers
GROUP BY owner
ORDER BY owner Asc;
我想制作业主和客户参数。我了解如何在 RPC 中添加“参数”,但我不确定如何将它们用作上述问题的列/表。
有谁知道如何在supabase函数中处理这个问题?
奖金问题:
我将如何去做这个加入?我根据接受的答案有以下内容,但失败了'column "customers.owner_id" does not exist'
我已经对这个函数进行了硬编码,它按预期工作,只是不使用下面的参数化版本:
CREATE OR REPLACE FUNCTION fetch_autocomplete_options(
table_name text,
join_table_name text,
join_column_name text,
table_column_name text,
join_table_column_name text
)
RETURNS TABLE ( value text) AS
$BODY$
BEGIN
RETURN QUERY EXECUTE format(
'SELECT DISTINCT %I::text FROM %I INNER JOIN %I ON %I=%I ORDER BY %I ASC;',
join_column_name, table_name, join_table_name, table_column_name, join_table_column_name, join_column_name
);
END;
$BODY$
LANGUAGE plpgsql;
弄清楚了!!!这是为了以防万一有人遇到这个奖金问题:
CREATE OR REPLACE FUNCTION fetch_autocomplete_options(
table_name text,
join_table_name text,
join_column_name text,
table_column_name text,
join_table_column_name text
)
RETURNS TABLE ( value text) AS
$BODY$
BEGIN
RETURN QUERY EXECUTE format(
'SELECT DISTINCT j.%I::text FROM %I as t INNER JOIN %I as j ON t.%I::text = j.%I::text ORDER BY j.%I::text ASC;',
join_column_name, table_name, join_table_name, table_column_name, join_table_column_name, join_column_name
);
END;
$BODY$
LANGUAGE plpgsql;