php - plpgsql调用存储过程并获取结果集

I am trying to make a stored procedure which returns rows from the user table. I call it from php with pg_ functions: pg_prepare($conn, $id, 'SELECT user_read_all()') and pg_execute($conn, $id, array()). Sadly I don't have PDO just for mysql.

I tried with this code, but I have multiple problems:

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;

By pg_fetch, the result is array('test' => string(...)) instead of array(user_id => 1, user_name => '', user_email => ''). I want to cut off the user_ prefix too, but I got syntax error by this:

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;

;-(

Is it possible to do this with plpgsql?

My goal is to make a stored procedure only interface, but I have serious doubts... With mysql this is very easy, but mysql has limited functions only compared to 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;

So the question is how to fix the stored procedure and the php result set?

solution

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;

and

SELECT * FROM test();

on php side.

It's a function returning a table, so you have to treat it like a table:

SELECT * FROM user_read_all();

Try both in psql and look carefully at the output. Your original way returns a set of row-types, the FROM way returns columns.

Regarding the syntax error - you don't give it, so I can't say.

Do avoid using reserved words for column/table-names though. Things like "type", "name", "user" etc. - see docs for details.

The real problem here is that user is a reserved keyword in PostgreSQL in addition to current_user, contrary to MySQL, where it's only current_user that is reserved.

The first form of your function didn't yield a syntax error at create time because the plpgsql parser didn't try to identify the columns. It certainly fails at runtime, though. If the php code didn't shout any error, its code for error detection is probably lacking.

user in PG returns your login name and can be used as if it was a table: SELECT * FROM user, or as a standalone expression: SELECT user,[other columns] FROM table...

Should you still want to use it as a table name, you must enclose it in double quotes: SELECT * FROM "user" (assuming it's been created lower case. Another effect of double quotes is that it turns on the exact case matching).

Also for a function like yours that just runs a query, you don't want to use the procedural plpgsql language but the more efficient sql language. In SQL the function would be:

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;

You may call it with SELECT user_read_all(), but SELECT * from user_read_all() is probably what you want to get 3 proper columns.

   $get_test = "SELECT test()";
    $test = pg_query($db_server, $get_test);
    while ($row = pg_fetch_array($test)) {
         $value = $row[0];
    }

    echo $value;