我想创建一个函数,根据表名返回主键的下一个值:
CREATE OR REPLACE FUNCTION next_pk (_table varchar) RETURNS INTEGER AS $$
BEGIN
RETURN EXECUTE 'SELECT COALESCE (MAX (primary_key), 0) + 1 FROM ' || quote_ident (_table) || ' limit 1';
END;
$$ LANGUAGE plpgsql;
select next_pk ('myTable');
但它给出了以下错误:
ERROR: type "execute" does not exist
LINE 1: SELECT EXECUTE 'SELECT COALESCE (MAX (primary_key), 0) + 1 F...
有线索吗?谢谢。
最佳答案
您需要整数变量和“execute into”:
CREATE OR REPLACE FUNCTION next_pk (_table varchar) RETURNS INTEGER AS $$
DECLARE
result integer;
BEGIN
EXECUTE
'SELECT COALESCE (MAX (primary_key), 0) + 1 FROM ' || quote_ident (_table)
INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;
select next_pk ('myTable');
关于function - 使用execute创建函数(plpgsql),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/14636869/