postgresql 我想让我的 pl/pgsql 脚本输出到屏幕上
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11890138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
I want to have my pl/pgsql script output to the screen
提问by Richard Hum
I have the following script that I want output to the screen from.
我有以下脚本，我希望从中输出到屏幕。
CREATE OR REPLACE FUNCTION randomnametest() RETURNS integer AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM my_table LOOP
SELECT levenshtein('mystring',lower('rec.Name')) ORDER BY levenshtein;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
I want to get the output of the levenshein() function in a table along with the rec.Name. How would I do that? Also, it is giving me an error about the line where I call levenshtein(), saying that I should use perform instead.
我想在表中获取 levenshein() 函数的输出以及 rec.Name。我该怎么做？此外，它给我一个关于我调用 levenshtein() 的行的错误，说我应该使用 perform 代替。
回答by AnBisw
Assuming that you want to insertthe function's return value and the rec.nameinto a different table. Here is what you can do (createthe table new_tabfirst)-
假设你想把insert函数的返回值和rec.name放入不同的表中。这是你可以做的（create表格new_tab第一）-
SELECT levenshtein('mystring',lower(rec.Name)) AS L_val;
INSERT INTO new_tab (L_val, rec.name);
The usage above is demonstrated below.
下面演示上面的用法。
I guess, you can use RAISE INFO 'This is %', rec.name;to view the values.
我想，您可以使用RAISE INFO 'This is %', rec.name;来查看值。
CREATE OR REPLACE FUNCTION randomnametest() RETURNS integer AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM my_table LOOP
SELECT levenshtein('mystring',lower(rec.Name))
AS L_val;
RAISE INFO '% - %', L_val, rec.name;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
Note- the FROMclause is optional in case you select from a function in a select like netxval(sequence_name)and don't have any actual table to select from i.e. like SELECT nextval(sequence_name) AS next_value;, in Oracle terms it would be SELECT sequence_name.nextval FROM dual;or SELECT function() FROM dual;. There is no dualin postgreSQL.
注意-FROM如果您从 select like 中的函数中选择netxval(sequence_name)并且没有任何实际表可供选择 ie like SELECT nextval(sequence_name) AS next_value;，则该子句是可选的，在 Oracle 术语中它将是SELECT sequence_name.nextval FROM dual;or SELECT function() FROM dual;。有没有dual在postgreSQL。
I also think that the ORDER BYis not necessary since my assumption would be that your function levenshtein()will most likely return only one value at any point of time, and hence wouldn't have enough data to ORDER.
我还认为ORDER BY没有必要，因为我的假设是您的函数levenshtein()很可能在任何时间点都只返回一个值，因此没有足够的数据到ORDER.
回答by Erwin Brandstetter
Ifyou want the output from a plpgsql function like the title says:
如果您想要 plpgsql 函数的输出，如标题所述：
CREATE OR REPLACE FUNCTION randomnametest(_mystring text)
RETURNS TABLE (l_dist int, name text) AS
$BODY$
BEGIN
RETURN QUERY
SELECT levenshtein(_mystring, lower(t.name)), t.name
FROM my_table t
ORDER BY 1;
END;
$$ LANGUAGE plpgsql;
- Declare the table with
RETURNS TABLE. - Use
RETURN QUERYto return records from the function. - Avoid naming conflicts between column names and
OUTparameters (from theRETURNS TABLEclause) by table-qualifying column names in queries.OUTparameters are visible everywhere in the function body. - I made the string to compare to a parameter to the function to make this more useful.
- 用 声明表
RETURNS TABLE。 - 用于
RETURN QUERY从函数返回记录。 - 通过查询中的表限定列名，避免列名和
OUT参数（来自RETURNS TABLE子句）之间的命名冲突。OUT参数在函数体中随处可见。 - 我将字符串与函数的参数进行比较，以使其更有用。
There are other ways, but this is the most effective for the task. You need PostgreSQL 8.4 or later.
还有其他方法，但这是最有效的任务。您需要 PostgreSQL 8.4 或更高版本。
For a one-time use I would consider to just use a plain query (= function body without the RETURN QUERYabove).
对于一次性使用，我会考虑只使用普通查询（= 没有RETURN QUERY上述内容的函数体）。
