current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
and it worked... thank you! –  tohaz Feb 3 at 15:38
add comment
up vote 2 down vote

@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.

share|improve this answer
    
Thanks for advices and your code style advices seem useful. I've skipped search_path when describing a problem as it looked too unrelated to the problem. –  tohaz Feb 4 at 6:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.