CODEWITHSUNDEEP
postgresqlcastingplpgsql
Louis93
Louis93

Reputation: 3923

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?

Upvotes: 4

Views: 4831

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657192

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
  • Don't quote the language name. It's an identifier.
  • A table does not have "arguments" but columns.
  • You are not passing a "table parameter to this function", but a row value. This is how you pass a table by name:
  • You can't "pass a whole table" as parameter directly in Postgres, there are no table variables. You would use a cursor or a temp table for that.

Upvotes: 7

Quassnoi
Quassnoi

Reputation: 425491

If all types in waypoint and temp are convertible to text, you can serialize to and deserialize from text:

SELECT  everything(temp::text::waypoint)
FROM    temp

However, an explicit construction would be cleaner:

SELECT  everything((col1, col2, col3, ...)::waypoint)
FROM    temp

or creating a CAST:

CREATE FUNCTION temp_to_waypoint (temp)
RETURNS waypoint
AS
$$
        SELECT  (col1, col2, col3, ...)::waypoint
$$
LANGUAGE 'sql';

CREATE CAST (temp AS waypoint) WITH FUNCTION temp_to_waypoint (test) AS IMPLICIT;

SELECT  everything(temp)
FROM    temp;

Upvotes: 1

Related Questions