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

Question

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.

Answer 1

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.