CODEWITHSUNDEEP
postgresqlpolymorphism
Eugen Konkov
Eugen Konkov

Reputation: 25282

How to return any* type from postgres function?

I can returns setof record and provide definition list to get result:

select (f.row).* from tree( 'resource_type' ) f( id int, path int[], fullname text, level int, row resource_type );

But how to rewrite my function to return any type with automatic cast?

This does not work:

create or replace function tree( _tbl regclass )
--returns setof record
returns table( id int, path int[], fullname text, level int, r record )
as $$
BEGIN
return query execute format( '
WITH RECURSIVE parent ( id, path, fullname, level, row ) AS (
    SELECT
      id,
      ARRAY[ id ],
      ''/'' || name::text,
      1,
      %1$s
    FROM %1$s
    WHERE parent_id = 13
    UNION ALL
    SELECT
      child.id,
      parent.path || child.id,
      parent.fullname || ''/'' || child.name,
      parent.level +1,
      child
    FROM parent
    JOIN %1$s child ON parent.id = child.parent_id
    WHERE NOT child.id = ANY(path)
)
SELECT * FROM parent ', _tbl);
END;
$$ LANGUAGE plpgsql

=> select f.r from tree( 'resource_type' ) f
ERROR:  structure of query does not match function result type
DETAIL:  Returned type resource_type does not match expected type record in column 5.
CONTEXT:  PL/pgSQL function tree(regclass) line 3 at RETURN QUERY

Upvotes: 0

Views: 2549

Answers (2)

Eugen Konkov
Eugen Konkov

Reputation: 25282

The solution is to pass anyelement as argument, then I can use anyelement for returns ... statement. doc

create or replace function tree( _tbl anyelement )
returns table( id int, path int[], fullname text, level int, "row" anyelement )
as $$
BEGIN
return query execute format( '...', pg_typeof( _tbl )::text );
END;
$$ LANGUAGE plpgsql

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 248225

The way to write a polymorphic function is to use anyelement. But you must provide an argument of type anyelement as well. The actual data type will then be deduced from the type of that argument:

CREATE FUNCTION poly(
   outtype anyelement,
   otherparam integer
) RETURNS TABLE (res anyelement)
   LANGUAGE plpgsql AS
$$DECLARE
   resulttype regtype := pg_typeof(outtype);
   whetever text;
BEGIN
   /* calculate a text representation of the result in "whatever" */
   EXECUTE format('SELECT CAST ($1 AS %s)', resulttype)
      USING whatever INTO res;
   RETURN NEXT;
END;$$

You call a function like this by supplying a dummy argument of the desired type:

SELECT * FROM poly(NULL::myresulttype, 42);

Upvotes: 3

Related Questions