将输入参数作为存储过程中的值传递

Question

我正在使用SQL(plpgsql(。我创建了一个存储过程，它接受像这样的varchar值"DUMMYVAR"。我想使用输入值("DUMMYVAR"(来填充表的整列，但是，当我尝试运行它时出现的输出错误，存储过程会发出"DUMMYVAR"不是列的信号。

下面是一个存储过程示例：

CREATE OR REPLACE PROCEDURE my_procedure(myvar VARCHAR)
AS $$
BEGIN
INSERT INTO mytable (var1, var2, dummyvar)
SELECT 'HELLO', 'WORLD', myvar
FROM othertable;
END;
$$

假设othertable和myvar = 'DUMMYVAR'中有5条记录，我期望的是以下输出：

var1    var2    dummyvar
HELLO   WORLD   DUMMYVAR
HELLO   WORLD   DUMMYVAR
HELLO   WORLD   DUMMYVAR
HELLO   WORLD   DUMMYVAR
HELLO   WORLD   DUMMYVAR

Answer 1

似乎对我来说很好

CREATE TEMP TABLE test_source (
row_id INTEGER IDENTITY 
, col_1 VARCHAR
);
INSERT INTO test_source (col_1)
VALUES ('hello world')
, ('hello world')
, ('hello world')
, ('hello world')
, ('hello world')
;
CREATE TEMP TABLE test_target (
row_id INTEGER 
, col_1 VARCHAR
, col_2 VARCHAR
);
CREATE OR REPLACE PROCEDURE sp_insert_test( test_value VARCHAR)
AS $$
BEGIN
INSERT INTO test_target (row_id, col_1, col_2)
SELECT *, test_value
FROM test_source;
END;
$$ LANGUAGE plpgsql;
;
CALL sp_insert_test ('test-test-test')
;
SELECT * FROM test_target ORDER BY 1
;
row_id |    col_1    |     col_2
--------+-------------+----------------
1 | hello world | test-test-test
2 | hello world | test-test-test
3 | hello world | test-test-test
4 | hello world | test-test-test
5 | hello world | test-test-test