postgresqlcastingplpgsql

How do I pass in a table parameter to this function?

I have a function organized like so: 

create function everything(waypoints waypoint)
  returns table(node int, xy text array) as $$
BEGIN
    create view results as ...

    return query (select * from results);
END;
$$ LANGUAGE plpgsql;

And I have a table that has arguments organized the way the waypoint data type is structured. This table is not explicitly of type waypoint itself.

The function gets created as it should, however, I am unable to call it by passing in my table like so:

select everything(waypoints);

Or select everything(select * from temp);

But it says syntax error at or near select for the latter and column waypoints does not exist for the former.

How do I proceed?

Solution

  • Postgres has some weak spots in the syntax for handling ROW types. You cannot cast from a table (alias) directly - unless a cast between source and target row type has been defined. Still true in Postgres 17:

    SELECT w::waypoint FROM waypoints w;
SELECT (w.*)::waypoint FROM waypoints w;

    ERROR:  cannot cast type waypoints to waypoint

    The solution is only one step away: decompose the row in a subquery or with an explicit ROW constructor, then the cast works.

    The hidden explanation: there is no cast defined between the row types waypoints and waypoint. But after converting the input row to an anonymous record first, Postgres tries and succeeds in casting to any compatible row type.

    This way, we skip casting to text and back, no need to list all columns individually, and no need to create a custom cast, either:

    SELECT (w.*)::waypoint FROM (SELECT * FROM waypoints) w;

    Shorter:

    SELECT w.*::waypoint FROM (TABLE waypoints) w;

    Shorter, yet (but w.* avoids possible naming conflicts):

    SELECT w::waypoint FROM (TABLE waypoints) w;

    Shortest:

    SELECT ROW(w.*)::waypoint FROM waypoints w;

    fiddle
    Old sqlfiddle

    Related:

    Each is shorter and faster. 10x faster than casting to text and back in a quick test with 50k rows and simple types. With (big) jsonb columns or any complex type (expensive conversion to/from text), the difference will be more pronounced.

    Avoid the problem

    Typically you don't need another custom composite type. Every table already has its row defined as type automatically. Just use the existing type waypoints instead of waypoint (if at all possible). Then all you need is:

    SELECT w FROM waypoints w;

    Or, to avoid possible conflicts with a column also named w:

    SELECT (w.*)::waypoints FROM waypoints w;

    For your example:

    SELECT everything(t) FROM temp t;  -- using type waypoints
SELECT everything(ROW(w.*)::waypoint) FROM temp t;  -- using type waypoint

    Function

    Your function has an invalid type declaration and is needlessly complex. I seriously doubt you want to create a view:

    CREATE FUNCTION everything(_wp waypoint)  -- or use type waypoints
  RETURNS TABLE(node int, xy text[])
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT ...
END
$func$;

    text array is not valid syntax, using text[] instead to declare an array of text.

    Don't use the table / type name waypoints as function parameter name, opens you up to confusing errors.

    Or just a simple SQL function for the simple case:

    CREATE FUNCTION everything(_wp waypoint)  -- or use type waypoints
  RETURNS TABLE(node int, xy text[])
  LANGUAGE sql AS
$func$
SELECT ...
$func$;
    Asides