sql - 一个 SQL 函数调用中的多个 ALTER TABLE ADD COLUMN

Question

我遇到了一些我想了解的奇怪行为。

我创建了一个 plpgsql 函数，除了 ALTER TABLE ADD COLUMN 之外什么都不做。我在同一张 table 上调用了 2 次:

• A) 在单个 SELECT 句子中
• B) 在具有与 A) 相同的 SELECT 的 SQL 函数中

结果不同:A) 创建两列，而 B) 仅创建一列。为什么？

代码:

CREATE FUNCTION add_text_column(table_name text, column_name text) RETURNS VOID
LANGUAGE plpgsql
AS $fff$
BEGIN
    EXECUTE '
        ALTER TABLE ' || table_name  || '
        ADD COLUMN  ' || column_name || ' text;
    ';
END;
$fff$
;
--  this function is called only in B
CREATE FUNCTION add_many_text_columns(table_name text) RETURNS VOID
LANGUAGE SQL
AS $fff$
    WITH
    col_names (col_name) AS (
        VALUES
            ( 'col_1' ),
            ( 'col_2' )
    )
    SELECT  add_text_column(table_name, col_name)
    FROM    col_names
    ;
$fff$
;

-- A)
CREATE TABLE a (id integer);

WITH
col_names (col_name) AS (
    VALUES
        ( 'col_1' ),
        ( 'col_2' )
)
SELECT  add_text_column('a', col_name)
FROM    col_names
;
SELECT * FROM a;

-- B)
CREATE TABLE b (id integer);
SELECT add_many_text_columns('b');
SELECT * FROM b;

结果:

CREATE FUNCTION
CREATE FUNCTION
CREATE TABLE
 add_text_column
-----------------


(2 rows)

 id | col_1 | col_2
----+-------+-------
(0 rows)

CREATE TABLE
 add_many_text_columns
-----------------------

(1 row)

 id | col_1
----+-------
(0 rows)

我正在使用 PostgreSQL 10.4。请注意，这只是一个最小的工作示例，并不是我需要的全部功能。

Answer 1

CREATE OR REPLACE FUNCTION g(i INTEGER)
RETURNS VOID AS $$
BEGIN
        RAISE NOTICE 'g called with %', i;
END 
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION t(i INTEGER)
RETURNS VOID AS $$
        SELECT g(id)
        FROM generate_series(1, i) id;
$$ LANGUAGE SQL;

当我运行 SELECT t(4) 时，你认为会发生什么？ g() 打印的唯一语句是 g called with 1。

这是因为您的 add_many_text_columns 函数返回单个结果 (void)。因为它是 SQL 并且只是返回 SELECT 语句的结果，所以它似乎在获得第一个结果后停止执行，如果您考虑一下，这是有道理的 - 它毕竟只能返回一个结果。

现在将函数更改为:

CREATE OR REPLACE FUNCTION t(i INTEGER)
RETURNS SETOF VOID AS $$
        SELECT g(id)
        FROM generate_series(1, i) id;
$$ LANGUAGE SQL;

然后再次运行 SELECT t(4)，现在打印出来了:

g called with 1
g called with 2
g called with 3
g called with 4

因为该函数现在返回 SETOF VOID，所以它不会在第一个结果后停止并完全执行它。

所以回到您的函数，您可以更改您的 SQL 函数以返回 SETOF VOID，但这并没有多大意义 - 我认为最好更改它到 plpgsql 并让它执行 PERFORM:

CREATE OR REPLACE FUNCTION t(i INTEGER)
RETURNS VOID AS $$
BEGIN
        PERFORM g(id)
        FROM generate_series(1, i) id;
END
$$ LANGUAGE plpgsql;

这将完全执行该语句并且它仍然返回单个 VOID。