postgresqltable-functions

Defining a psql C function gives a column definition list is required for functions returning "record"

I am trying to write a function for Postgres 9.6 that access some code written in C.

My function is defined as:

CREATE OR REPLACE FUNCTION graph_cluster_graph (sql text, has_rcost boolean)
  RETURNS SETOF RECORD AS
    '$libdir/libpgrouting-2.4', 'dir_graph_cluster_desc'
    LANGUAGE c STABLE STRICT;

When trying to invoke it with:

select * from   graph_cluster_graph('select * from case3_cab_dist_table',true);

I get the error

definition list is required for functions returning "record"

It works if I write

select graph_cluster_graph('select * from case3_cab_dist_table',true);

In this case, it invokes the requested C function and then bails out when it returns.

I did change the name of the C function just to find out what would happen (you can an error about being unable to find the function). So I know it can find my C routine. Again using the 2nd form of the select statement and get a response means that the C function has been correctly invoked.

Has anybody any idea what I am doing wrong ?

Solution

  • With a function that RETURNS SETOF RECORD the query parser does not know which columns the result rows will have, and you will have to supply that information in the query.

    Like the documentation says,

    In some cases it is useful to define table functions that can return different column sets depending on how they are invoked. To support this, the table function can be declared as returning the pseudotype record. When such a function is used in a query, the expected row structure must be specified in the query itself, so that the system can know how to parse and plan the query. This syntax looks like:

    function_call [AS] alias (column_definition [, ... ]) function_call AS [alias] (column_definition [, ... ]) ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

    If you know in advance what columns the record will have, it is better to defined the function as

    RETURNS SETOF datatype

    or

    RETURNS TABLE ( column_name column_type [, ...] )

    The latter is a shorthand for the older syntax:

    funcname(..., OUT column_name column_type, ...) RETURNS SETOF record