plpgsql调用存储过程并获取结果集 - plpgsql calling stored procedure and fetching result set

plpgsql calling stored procedure and fetching result set

我正在尝试创建一个存储过程，该过程将返回用户表中的行。我用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个适当列的内容。