PL/Proxy returning Unsupported Type on Stored Procedure Call

Question

I've setup a Stored Procedure in PL/Proxy to make a query, and receive some RECORDs back.

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 Partition:

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've likely guessed, this is hitting a defined SERVER in PGSQL called 'autocompletecluster'. The query string that I'm sending through is as follows:

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

It's returning the following:

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

The table that query is hitting 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 suggests that PL/Proxy doesn't support SETOF RECORD. Try instead defining your functions to return autocomplete%rowtype or, failing that, RETURNS TABLE (...) with a matching columns-set.