我正在尝试创建一个存储过程,该过程将返回用户表中的行。我用pg_函数从php调用它:pg_prepare($conn, $id, 'SELECT user_read_all()')
和pg_execute($conn, $id, array())
。遗憾的是,我没有只针对mysql的PDO。
我尝试过这个代码,但我有多个问题:
CREATE OR REPLACE FUNCTION user_read_all()
RETURNS table(user_id INT, user_name VARCHAR, user_email VARCHAR)
AS
$BODY$
BEGIN
return query SELECT
user_id, user_name, user_email
FROM
user;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
通过pg_fetch,结果是array('test' => string(...))
而不是array(user_id => 1, user_name => '', user_email => '')
。我也想去掉user_
前缀,但我遇到了语法错误:
CREATE OR REPLACE FUNCTION user_read_all()
RETURNS table(id INT, name VARCHAR, email VARCHAR)
AS
$BODY$
BEGIN
return query SELECT
user_id as id, user_name as name, user_email as email
FROM
user;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-(
用plpgsql可以做到这一点吗?
我的目标是制作一个仅限存储过程的接口,但我有严重的疑问。。。有了mysql,这很容易,但mysql的功能仅限于pgsql。。。
CREATE PROCEDURE `user_read_all`()
BEGIN
SELECT `user_id` AS `id`, `user_name` AS `name`, `user_email` AS `email` FROM `user`
ORDER BY `user_id` DESC;
END;
所以问题是如何修复存储过程和php结果集?
解决方案
CREATE FUNCTION test ()
RETURNS TABLE (id INT, name VARCHAR, email VARCHAR)
AS
$BODY$
BEGIN
return QUERY SELECT
"user".user_id, "user".user_name, "user".user_email
FROM
"user";
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
和
SELECT * FROM test();
在php端。
这是一个返回表的函数,因此必须将其视为表:
SELECT * FROM user_read_all();
在psql中尝试这两种方法,并仔细查看输出。原始方式返回一组行类型,FROM方式返回列。
关于语法错误,你没有给出,所以我不能说。
不过,请避免在列/表名称中使用保留字。诸如"类型"、"名称"、"用户"等-请参阅文档了解详细信息。
这里真正的问题是user
是PostgreSQL中除了current_user
之外的保留关键字,而MySQL中只有current_user
是保留关键字。
函数的第一种形式在创建时没有产生语法错误,因为plpgsql解析器没有尝试识别列。不过,它在运行时肯定会失败。如果php代码没有喊出任何错误,那么可能缺少用于错误检测的代码。
PG中的user
返回您的登录名,可以像使用表一样使用:SELECT * FROM user
,或作为独立表达式:SELECT user,[other columns] FROM table...
如果您仍然想将其用作表名,则必须将其用双引号括起来:SELECT * FROM "user"
(假设它是用小写创建的。双引号的另一个效果是启用完全的大小写匹配)。
同样,对于像您这样只运行查询的函数,您不希望使用过程plpgsql
语言,而是希望使用更高效的sql语言。在SQL中,函数为:
CREATE OR REPLACE FUNCTION user_read_all()
RETURNS table(id INT, name VARCHAR, email VARCHAR)
AS
$BODY$
SELECT
user_id as id, user_name as name, user_email as email
FROM
"user";
$BODY$
LANGUAGE sql;
您可以将其称为SELECT user_read_all()
,但SELECT * from user_read_all()
可能是您想要获得3个适当列的内容。