CREATE TABLE t(rid int, foo int);
INSERT INTO t VALUES (1,1),(2,2),(3,3);
CREATE FUNCTION fnn(id int, new_val int) RETURNS void AS $$
BEGIN
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' AND COLUMN_NAME = 'bar') THEN
ALTER TABLE t
ADD bar int;
END IF;
UPDATE t
SET bar = new_val
WHERE id = rid;
END; $$
LANGUAGE plpgsql;
SELECT fnn(1, 15);
SELECT * FROM t;
rid | foo | bar
--: | --: | ---:
2 | 2 | <em>null</em>
3 | 3 | <em>null</em>
1 | 1 | 15
您可以使用 INFORMATION_SCHEMA.COLUMNS 检查列是否存在。
db<>在这里摆弄