CODEWITHSUNDEEP
phpsqlpostgresqlstored-proceduresplpgsql
tohaz
tohaz

Reputation: 197

Postgresql custom function returning table

I am using PostgreSQL 9.1.11.
I need to return result of SELECT to my php script. The invocation in php is like this:

$res = $pdb->getAssoc("SELECT * FROM my_profile();");

The class code to illustrate what is going on in php

public function getAssoc($in_query) {
  $res = pg_query($this->_Link, $in_query);
  if($res == FALSE) {
    return array("dberror", iconv("utf-8", "windows-1251", pg_last_error($this->_Link)));
  }
  return pg_fetch_all($res);
}

Next comes my function in Postgres. I fully re-create database by dropping in a script when I update any function. (The project is in the early stage of development.) I have little to no experience doing stored procedures.

I get this error:

structure of query does not match function result type
CONTEXT: PL/pgSQL function "my_profile" line 3 at RETURN QUERY )

Trying to write:

CREATE FUNCTION my_profile()
RETURNS TABLE (_nick text, _email text) AS $$
BEGIN
  RETURN QUERY SELECT (nick, email) FROM my_users WHERE id = 1;
END;
$$
LANGUAGE 'plpgsql' SECURITY DEFINER;

Table structure is:

CREATE TABLE my_users(
  id integer NOT NULL,
  nick text,
  email text,
  pwd_salt varchar(32),
  pwd_hash character(128),
  CONSTRAINT users_pk PRIMARY KEY (id)
);

When I return 1 column in a table the query works. Tried to rewrite procedure in LANGUAGE sql instead of plpgsql with some success, but I want to stick to plpgsql.

The Postgres 9.1.11, php-fpm I am using is latest for fully updated amd64 Debian wheezy.

What I want to do is to return a recordset containing from 0 to n rows from proc to php in an associative array.

Upvotes: 2

Views: 3529

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

@Daniel already pointed out your immediate problem (incorrect use of parentheses). But there is more:

  • Never quote the language name plpgsql in this context. It's an identifier, not a string literal. It's tolerated for now since it's a wide-spread anti-pattern. But it may be considered a syntax error in future releases.

  • The SECURITY DEFINER clause should be accompanied by a local setting for search_path. Be sure to read the according chapter in the manual.

Everything put together, it could look like this:

CREATE FUNCTION my_profile()
  RETURNS TABLE (nick text, email text) AS
$func$
BEGIN
   RETURN QUERY
   SELECT m.nick, m.email FROM my_users m WHERE m.id = 1;
END
$func$
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, pg_temp;

Replace public whit the actual schema of your table.

To avoid possible naming conflicts between OUT parameters in RETURNS TABLE ... and table columns in the SELECT statement I table-qualified column names with the given alias m.

Upvotes: 3

Daniel Vérité
Daniel Vérité

Reputation: 61506

This part is incorrect:

RETURN QUERY SELECT (nick, email) FROM my_users WHERE id = 1;

You should remove the parentheses around nick,email otherwise they form a unique column with a ROW type.

This is why it doesn't match the result type.

Upvotes: 4

Related Questions