PL / Proxy return Unsupported type when calling stored procedure

Question

I installed the stored procedure in PL / Proxy to execute the request and return some RECORD.

In PL / Proxy:

CREATE OR REPLACE FUNCTION query_autocomplete(q text, i_id bigint) RETURNS SETOF RECORD AS $$ CLUSTER 'autocompletecluster'; RUN ON i_id; $$ LANGUAGE plproxy; 

In each section:

 CREATE OR REPLACE FUNCTION query_autocomplete(q text, i_id bigint) RETURNS SETOF RECORD AS $$ DECLARE rec RECORD; BEGIN FOR rec IN EXECUTE q LOOP RETURN NEXT rec; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; 

As you probably guessed, this hits a particular SERVER in PGSQL called "autocompletclluster". The query string I'm sending is as follows:

 $sql = "SELECT * FROM autocomplete WHERE member_id = :memberId"; $query = $this->db->prepare("SELECT query_autocomplete('{$sql}',1234"); 

It returns the following:

 SQLSTATE[XX000]: Internal error: 7 ERROR: PL/Proxy function public.query_autocomplete(0): unsupported type 

The table in which the query is executed is defined as such:

 CREATE TABLE autocomplete ( id character varying(100) NOT NULL, extra_data hstore, username character varying(254), member_id bigint ); 

What am I doing wrong?

Answer 1

The error strongly indicates that PL / Proxy does not support SETOF RECORD . Instead, try defining your functions to return autocomplete%rowtype or, if not, RETURNS TABLE (...) with the appropriate set of columns.