需要打印 Postgresql 数据库控制台
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17693259/
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
Need to Printing Postgresql database console
提问by Kannan
Postgresql database store procedure insert query and i need print the raise notice the input values
Postgresql 数据库存储过程插入查询，我需要打印提高通知输入值
CREATE OR REPLACE FUNCTION new(pk character varying, u character varying,ps character varying)
RETURNS SETOF record AS
$BODY$
BEGIN
RAISE NOTICE 'PK is %','--'pk;
RAISE NOTICE 'Username is %','--'u;
RAISE NOTICE 'Password is %','--'ps;
EXECUTE 'INSERT INTO table_sp("pk_id","username","password")VALUES ('''||pk||''' ,'''||u||''','''||ps||''')' ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION new(character varying, character varying,character varying)
OWNER TO postgres;
UserManualInputs:
用户手册输入：
select new('2','admin','admin');
I gave the manual input through select new('2','admin','admin')and i need print in PostgreSQL console input values using raise notice like (RAISE NOTICE 'PK is %','--'pk)
我提供了手动输入select new('2','admin','admin')，我需要使用 raise notice 在 PostgreSQL 控制台输入值中打印(RAISE NOTICE 'PK is %','--'pk)
回答by Pavel Stehule
Craig Ringer replied to you, but I have to attach a notice.
Craig Ringer 回复了你，但我必须附上通知。
Using dynamic SQL (EXECUTE statement) in your example is very wrong idea, and badly implementation too.
在您的示例中使用动态 SQL（EXECUTE 语句）是非常错误的想法，而且实现也很糟糕。
- use simply SQL statement only. Use dynamic SQL only if you needed!
- 仅使用简单的 SQL 语句。仅在需要时才使用动态 SQL！
BEGIN
RAISE NOTICE '...';
INSERT INTO table_sp(pk_id, username, password)
VALUES(pk, u, ps);
END;
- you used a antipattern - your code is SQL injection vulnerable. Depends on PostgreSQL version that you use, you can use a more patterns how to write secure code:
- 您使用了反模式 - 您的代码容易受到 SQL 注入攻击。取决于您使用的 PostgreSQL 版本，您可以使用更多模式来编写安全代码：
-- very old (but secure, and more readable)
EXECUTE 'INSERT INTO table_sp (pk_id, username, password) VALUES ('
quote_literal(pk) || ',' quote_literal(u) || ',' || quote_literal(ps) || ')';
-- little bit modern (8.4) - secure, readable, and fast
EXECUTE 'INSERT INTO table_sp (pk_id, username, password) VALUES(,,)'
USING pk, u, ps
-- or modern (but USING clause is better and faster for this use case)
EXECUTE format('INSERT INTO table_sp(pk_id, username, password) VALUES(%L,%L,%L)',
pk, u, ps)
You newer can write code:
你新人可以写代码：
-- very very very bad code!!!!! EXECUTE 'some statement ''' || variable || ''' some other ';
sorry for offtopic.
抱歉离题。
回答by Craig Ringer
Assuming that by "PostgreSQL console" you mean psql, just make sure you SET client_min_messages = 'notice'or higher.
假设您的意思是“PostgreSQL 控制台” psql，只需确保您SET client_min_messages = 'notice'或更高。
Your syntax probably doesn't do what you expect, though. Demonstrating using DOinstead of full functions to keep things brief and simple:
不过，您的语法可能没有达到您的预期。演示使用DO而不是完整的函数来保持简洁：
regress=> DO
$$
DECLARE
pk integer := 4;
BEGIN
RAISE NOTICE 'PK is %','--'pk;
END;
$$;
NOTICE: PK is --
DO
Maybe you wanted:
也许你想要：
regress=> DO
$$
DECLARE
pk integer := 4;
BEGIN
RAISE NOTICE 'PK is --%',pk;
END;
$$;
NOTICE: PK is --4
DO
?
?
Beware that such messages will also get logged to the PostgreSQL system log files, so you should not reveal passwords in such messages in a production app.
请注意，此类消息也会记录到 PostgreSQL 系统日志文件中，因此您不应在生产应用程序中的此类消息中泄露密码。
BTW, newis a really bad name choice for a function; I suggest something that isn't a keyword in most languages including SQL.
顺便说一句，new对于函数来说，这是一个非常糟糕的名称选择；我建议在包括 SQL 在内的大多数语言中都不是关键字的东西。
