php - plpgsql calling stored procedure and fetching result set

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.

Richard Huxton · Accepted Answer · 2013-07-03 14:49:00Z

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.

answered Jul 3, 2013 at 14:49

Richard Huxton

23.5k5 gold badges42 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

inf3rno Over a year ago

Partially solved the problem, now I have a single row by fetch with just null values... The string was the null values concatenated... The user table is not empty, and when I use it quoted I got the same result...

inf3rno Over a year ago

I have to use "user".user_id instead of simple user_id. Now it returns the result, but I want to return it as id, name, email, not as user_id, user_name, user_email... I tried to add "as" for every row, but the result was syntax error...

Daniel Vérité · Accepted Answer · 2013-07-03 15:16:56Z

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.

"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." - Yes I can do it in simple sql, but I am learning plpgsql...
The quote does not really count by user. I mean I got the same result now a single row with just null values. The user table is not empty... The select * from is a step forward...
I accepted Richards answer because he wrote first. Btw you missed both that I have to use the table name by every column of the select query in plpgsql.

Collectives™ on Stack Overflow

php - plpgsql calling stored procedure and fetching result set

2 Answers 2

2 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Related