Postgres 14 or later

最简单的办法是采用新的标准SQyntax:

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
RETURN repeat( * , generate_series (1, n));

Or better (and all standard SQL):

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
BEGIN ATOMIC
SELECT repeat( * , g) FROM generate_series (1, n) g;
END;

“Better”因为更容易理解,而坚持标准(更便于运输)。 两者都是可争议的。 文件<代码>。 IMMUTABLE STRICT PARALLEL RSE appropriate, which would otherwisefall to VSOTILE CALLED ON NUL INPUT PARALLEL UNSAFE。 不可拆解。

Call:

SELECT asterisks(6);

Or, more explicitly and standard-conforming:

SELECT * FROM asterisks(6);

见:

• generate_series() in the Postgres manual
• Any downsides of using data type "text" for storing strings?
• What does BEGIN ATOMIC ... END mean in a PostgreSQL SQL function / procedure?

Postgres 13 (or any version):

职能:

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT repeat( * , generate_series (1, n));
$func$;

PL/pgSQL在休息期间(通行费通常更高):

CREATE OR REPLACE FUNCTION pg_temp.asterisks(n int)
  RETURNS SETOF text
  LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
FOR i IN 1..n LOOP
   RETURN NEXT repeat( * , i);
END LOOP;
END
$func$;

见:

• Difference between language sql and language plpgsql in PostgreSQL functions

当然,就简单而言,我只是简单地发言,而不是发挥职能:

SELECT repeat( * , generate_series (1, 3));

A SQL function:

• 可在<条码>A上操作多篇发言。 条款或BEGIN ATOECA; .END; 声明 获取带有电离器多种参数的多重价值。 rel=“nofollow noreferer”>PL/pgSQL职能: a)

• 只能有几类参数。

• 各项参数的具体内容如下:$1,$2, ......

• 可在<条码>、<条码>、<>条码>、<>条码>和<条码>> >。

• 可在以下网址上向打电话者提交最后发言:.RETURN statement not using AS 条款或使用<条码>BEGIN ATO中等收入国家;......END; 说明。

• 可在向打电话者作最后发言后再加一份::RETURN statement,不使用AS 条款或使用<条码>BEGIN ATO中等收入国家;......END; 说明。

• 不能有<条码>BEGIN ......END; 声明,由于存在错误,而PL/pgSQL功能可以有多种说明。 * 注意,<条码>BEGIN END; 声明不是transaction。

• 不能有DECLARE 条款,而PL/pgSQL的功能则没有出现错误。

• 不能控制与例如<代码>BEGIN;和COMMIT;的交易,因为有错误。

• 如果存在错误,则进行单一交易,则进行滚动支持。

* < rel=“nofollow noreferer” The doc interpret a SOL function in details and my interpret a PL/pgSQL function.

例如,你创建<代码>测试表格如下:

CREATE TABLE test (
  num INTEGER
);

然后插入num>的行文2。 如下所示:

INSERT INTO test (num) VALUES (2);

现在,你可以创建<条码>附加功能(INTEERT),在<条码>上添加<条码>、<条码>、<条码>和<<条码>对电传机添加<条码>。 * 您必须制定<代码>RETURNS <type>条或OUT或>。 SlectT num from test; 是收益价值,其类型实际上与> 相匹配。 数值也为INTEERT,这样,如果类型的tn匹配RETURNS <type>则有错误,最后,你必须设定<代码>SQL至>>>>>>LANGUAGE,以形成一种结构功能:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;

或者,你可以使用<条码>,代之以<条码>,从<条码>中选取。

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
UPDATE test SET num = num + value RETURNING num;
$$ LANGUAGE SQL;                  -- ↑ Here ↑

接着,请在<条码>后插入<条码>+(3)<>>>> > >。

postgres=# SELECT addition(3);
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

And, you can use VOID type to return nothing as shown below:

CREATE FUNCTION addition(value INTEGER) RETURNS VOID AS $$
UPDATE test SET num = num + value;            -- ↑ Here
SELECT num FROM test;
$$ LANGUAGE SQL;

Then, you can call addition(3) with SELECT statement, then 3 is added to num and nothing is returned as shown below:

postgres=# SELECT addition(3);
 addition
----------

(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

并且,你可以回馈一份没有使用<代码>AS的声明的数值。 下述条款。 * 待印发。 条款载于。 错误:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER LANGUAGE SQL
RETURN 2 + value;

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER /* LANGUAGE SQL */
RETURN 2 + value;

postgres=# SELECT addition(3);
 addition
----------
        5
(1 row)

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER LANGUAGE SQL
BEGIN ATOMIC; -- Here
UPDATE test SET num = num + value;
SELECT num FROM test;
END; -- Here

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER LANGUAGE SQL
BEGIN ATOMIC;
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
RETURN value
END;

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER LANGUAGE SQL
BEGIN ATOMIC;
UPDATE test SET num = num + value RETURNING num;
END;

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER /* LANGUAGE SQL */
BEGIN ATOMIC;
...
END;

postgres=# SELECT addition(3);
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

And, you can use $1(num1), $2(num2), ... as the aliases of parameters as shown below. *My answer explains the aliases of paremeters and how to declare local variables in detail:

CREATE FUNCTION addition(num1 INTEGER, num2 INTEGER) RETURNS INTEGER AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;

页: 1

CREATE FUNCTION addition(INTEGER, INTEGER) RETURNS INTEGER AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;

然后,请打电话addition(2, 3) with SlectT statement,然后打电话5<>。

postgres=# SELECT addition(2, 3);
 addition
----------
        5
(1 row)

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
BEGIN
UPDATE test SET num = num + value;
SELECT num FROM test;
END;
$$ LANGUAGE SQL;

并且,如果使用与例如<代码>BEGIN;和COMMIT; in akou function中的交易,则出现以下错误。 * 包含<代码> VOID的功能。 字体有误,但称职结构功能有误:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
BEGIN;
UPDATE test SET num = num + value;
SELECT num FROM test;
COMMIT;
$$ LANGUAGE SQL;

ERROR: return type mismatch in function declared to return integer
DETAIL: Function s final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
CONTEXT: SQL function "addition"

而且,如果使用<代码>DECLARE,则在一项职能中出现如下错误:

CREATE FUNCTION addition() RETURNS INTEGER AS $$
DECLARE
  value INTEGER := 9;
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;

ERROR: syntax mis at or near “INTEERT”

此外,您可使用<代码>IN,OUT和INOUT参数,其功能如下所示。

。

CREATE FUNCTION addition(IN value INTEGER) RETURNS INTEGER AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;

接着,请在<条码>后插入<条码>+(3)<>>>> > >。

postgres=# SELECT addition(3);
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

。 * 无需制定<代码>RETURNS <type>条,如果你设置了OUT/code>,参数 * 如果你制定了<条码>OUT para amount,你可以确定<条码>RETURNS <type>条:

CREATE FUNCTION addition(OUT value INTEGER) /* RETURNS INTEGER */ AS $$                     -- ↑ Here               -- ↑ Unset ↑
UPDATE test SET num = num + 3;
SELECT num FROM test;
$$ LANGUAGE SQL;

接着,请在<条码>上添加<>条码>(<>+>>>和<条码>。

postgres=# SELECT addition();
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

But, calling addition(3) gets the error as shown below because an OUT parameter cannot get a value from the caller as I said above:

postgres=# SELECT addition(3);
ERROR:  function addition(integer) does not exist
LINE 1: SELECT addition(3);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

并且,你仍然可以制定<代码>RETURNS <type>、/code>参数的条款,但这些参数的类型必须与以下所示类型相同:

CREATE FUNCTION addition(OUT value INTEGER) RETURNS INTEGER AS $$
UPDATE test SET num = num + 3;   -- ↑ Here        -- ↑ Here
SELECT num FROM test;
$$ LANGUAGE SQL;

因此,如果其类型不相同,则有以下错误:

CREATE FUNCTION addition(OUT value VOID) RETURNS INTEGER AS $$
UPDATE test SET num = num + 3;   -- ↑ Here       -- ↑ Here
SELECT num FROM test;
$$ LANGUAGE SQL;

经常资源:由于产出参数,功能结果类型必须无效

。

CREATE FUNCTION addition(INOUT value INTEGER) /* RETURNS INTEGER */ AS $$                       -- ↑ Here               -- ↑ Unset ↑
UPDATE test SET num = num + value;
SELECT num FROM test;
$$ LANGUAGE SQL;

接着,请在<条码>后插入<条码>+(3)<>>>> > >。

postgres=# SELECT addition(3);
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

A PL/pgSQL=:

• can run multiple SQL statements with BEGIN ... END; statement getting multiple values with multiple parameters from the caller while a SQL function cannot. *Be careful, BEGIN ... END; statement is not transaction.

• 只能有几类参数。

• 各项参数的具体内容如下:$1,$2, ......

• can have DECLARE clause while a SQL function cannot.

• can have IN, OUT and INOUT parameters.

• 可在:RETURN statement, a 或INOUT参赛人参数。

• 不能控制与例如<代码>BEGIN;和COMMIT;的交易,因为有错误。

• 如果存在错误,则进行单一交易,则进行滚动支持。

* < interpret a PL/pgSQL function in details and mywer, 解释了Q 功能。

CREATE TABLE test (
  num INTEGER
);

INSERT INTO test (num) VALUES (2);

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
BEGIN
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN value;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT addition(3);
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

CREATE FUNCTION addition(value INTEGER) RETURNS VOID AS $$
BEGIN                                         -- ↑ Here
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
RETURN; -- Here
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION addition(value INTEGER) RETURNS VOID AS $$
BEGIN                                         -- ↑ Here
UPDATE test set num = num + value;
SELECT num INTO value FROM test;
-- RETURN; -- Here
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT addition(3);
 addition
----------

(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

And, you can use $1(num1), $2(num2), ... as the aliases of parameters as shown below. *My answer explains the aliases of paremeters and how to declare local variables in detail:

CREATE FUNCTION addition(num1 INTEGER, num2 INTEGER) RETURNS INTEGER AS $$
BEGIN
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

*You can set parameters with only types as shown below:

CREATE FUNCTION addition(INTEGER, INTEGER) RETURNS INTEGER AS $$
BEGIN
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

* 可在<条码>下使用当地变量(<条码>值>)。 下述条款:

CREATE FUNCTION addition(INTEGER, INTEGER) RETURNS INTEGER AS $$
DECLARE
  value INTEGER;
BEGIN
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

Then, you can call addition(2, 3) with SELECT statement, then 5 is returned as shown below:

postgres=# SELECT addition(2, 3);
 addition
----------
        5
(1 row)

仔细使用<代码>VOID 类型RETURN Value; in a PL/pgSQL= 如下所示的错误,因为它们的类型> VOID和>>> INTEERT并不相同,而职称没有错误:

CREATE FUNCTION addition(value INTEGER) RETURNS VOID AS $$
BEGIN                                         -- ↑ Here
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
RETURN value; -- Error
END;
$$ LANGUAGE plpgsql;

ERROR: RETURN cannot have a parameter in function returning void
LINE 5: RETURN value; -- Here

并且不使用<条码>BEGIN END; in a PL/pgSQL 功能中的错误如下:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
-- BEGIN -- Error
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
RETURN value;
-- END; -- Error
$$ LANGUAGE plpgsql;

ERROR: syntax error at or near "UPDATE"
LINE 2: UPDATE test set num = num + value;

并且,使用与例如<代码>BEGIN;和COMMIT;载于PL/pgSQL功能的交易,其错误如下:

CREATE FUNCTION addition(value INTEGER) RETURNS INTEGER AS $$
BEGIN
BEGIN;
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
RETURN value;
COMMIT;
END;
$$ LANGUAGE plpgsql;

ERROR: syntax error at or near ";"
LINE 3: BEGIN;

此外,如以下所示,您可使用PL/pgSQL功能中的<代码>IN、>/code>。

An IN parameter can get a value from the caller but cannot return a value to the caller and actually, with and without IN is the same so addition(IN value INTEGER) and addition(value INTEGER) are the same:

CREATE FUNCTION addition(IN value INTEGER) RETURNS INTEGER AS $$
BEGIN                 -- ↑↑ Here
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
RETURN value;
END;
$$ LANGUAGE plpgsql;

然后请打电话addition(3) with SlectT statement,然后将5 退回,3在上添加<> 如下所示:

postgres=# SELECT addition(3);
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

OUT参数,你可以不打上<条码>RETURNS <type> 条款和RETURN 说明,如果你用<条码>发布<>><><>>><>>>> 代码>参数表示,则你必须打上<条码>RETURN;,以别无下列错误方式返回:

CREATE FUNCTION addition(OUT value INTEGER) /* RETURNS INTEGER */ AS $$
BEGIN                  -- ↑ Here               -- ↑ Unset ↑
UPDATE test SET num = num + 3;
SELECT num INTO value FROM test;
-- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;

然后请打电话addition(>> with SlectT statement,然后将5退回,3在上添加<>。 如下所示:

postgres=# SELECT addition();
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

但是,电话addition(3)发现以下错误:OUT/code> para amount不能从打电话者那里获得价值,正如我前面所说的那样:

postgres=# SELECT addition(3);
ERROR:  function addition(integer) does not exist
LINE 1: SELECT addition(3);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

并且,您仍可制定<代码>RETURNS <type> 和的说明,附<>OUT/code> para amount,但OUT para amount and RETURNS <type> clauses must be same and RETURN;< to 回去除下文所示其他错误外,不得退回:

CREATE FUNCTION addition(OUT value INTEGER) RETURNS INTEGER AS $$
BEGIN                  -- ↑ Here            -- ↑↑ Here ↑↑
UPDATE test SET num = num + 3;
SELECT num INTO value FROM test;
RETURN; -- Here
END;
$$ LANGUAGE plpgsql;

因此,如果<条形码>的类型(<>条码><>>><>>>>和<条码>>>> 以及>条码>;型号与“条码”条款(<条码>>>>)的类型不同,则该错误如下:

CREATE FUNCTION addition(OUT value INTEGER) RETURNS VOID AS $$
BEGIN                              -- ↑ Here      -- ↑ Here
UPDATE test SET num = num + 3;
SELECT num INTO value FROM test;
-- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;

由于产出参数,因此功能结果类型必须重新分类。

并且,在<条形码>(<>->>>->-->--------->参数上,设定了<条码>(VOID> > 类型至<条码>>>>>>>>>>>>>> 深陷错误,如下所示:

CREATE FUNCTION addition(OUT value VOID) AS $$
BEGIN                            -- ↑ Here
UPDATE test SET num = num + 3;
SELECT num INTO value FROM test;
-- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;

ERROR: PL/pgSQL functions cannot accept type void

And, setting RETURN value; instead of RETURN; with an OUT parameter gets the error as shown below:

CREATE FUNCTION addition(OUT value INTEGER) AS $$
BEGIN                  -- ↑ Here
UPDATE test SET num = num + 3;
SELECT num INTO value FROM test;
RETURN value;
END;  -- ↑ Here
$$ LANGUAGE plpgsql;

ERROR: RETURN cannot have a parameter in function with OUT parameters
LINE 5: RETURN value;

。

CREATE FUNCTION addition(INOUT value INTEGER) /* RETURNS INTEGER */ AS $$
BEGIN                   -- ↑ Here                -- ↑ Unset ↑
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
-- RETURN; -- Unset
END;
$$ LANGUAGE plpgsql;

然后请打电话addition(3) with SlectT statement,然后将5 退回,3在上添加<> 如下所示:

postgres=# SELECT addition(3);
 addition
----------
        5
(1 row)
postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)