title: php - plpgsql calling stored procedure and fetching result set
body: <p>I am trying to make a stored procedure which returns rows from the user table.
  I call it from php with pg_ functions: <code>pg_prepare($conn, $id, 'SELECT user_read_all()')</code> and <code>pg_execute($conn, $id, array())</code>. Sadly I don't have PDO just for mysql.</p>
  
  <p>I tried with this code, but I have multiple problems:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>By pg_fetch, the result is <code>array('test' =&gt; string(...))</code> instead of <code>array(user_id =&gt; 1, user_name =&gt; '', user_email =&gt; '')</code>. I want to cut off the <code>user_</code> prefix too, but I got syntax error by this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>;-(</p>
  
  <p>Is it possible to do this with plpgsql?</p>
  
  <p>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...</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>So the question is how to fix the stored procedure and the php result set?</p>
  
  <p><strong>solution</strong></p>
  
  <pre><code>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;
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT * FROM test();
  </code></pre>
  
  <p>on php side.</p>