How do I pass a table parameter to this function?
I have a function organized like this:
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 where the arguments are organized the way the data type is structured waypoint
. This table is clearly not of type waypoint
.
The function is created as it should, however I cannot call it by going into my table like this:
select everything(waypoints);
Or select everything(select * from temp);
But he says there is a syntax error at or near select
for the latter and the column waypoints
does not exist for the former.
How can I proceed?
source to share
Everything is tested in Postgres 9.4 .
Postgres has several syntax weaknesses for handling ROW types. You cannot dump directly from the table (alias):
SELECT w::waypoint FROM waypoints w;
ERROR: cannot cast type waypoints to waypoint
The solution is just one step away: expand the string into a subquery, then the job is done. This way the column values are decomposed and transferred to the new type directly, without pushing on text
and back. You don't need to list all the columns individually, and you don't need to create a custom listing, either:
SELECT (w.*)::waypoint FROM (SELECT * FROM waypoints) w;
Or shorter:
SELECT w.*::waypoint FROM (TABLE waypoints) w;
Or shorter, but:
SELECT w::waypoint FROM (TABLE waypoints) w;
It is shorter and faster, when tested quickly with 30k lines and simple types, it is 10x faster than text
back and forth. If you have (large) columns jsonb
or any complex type (expensive to / from conversion text
) the difference will be much larger.
More importantly, you don't need another custom composite (ROW) type. Each table already has its own row, defined as type automatically. Just use the existing type waypoints
instead waypoint
(if at all possible). Then you only need:
SELECT w FROM waypoints w;
Or for your example:
SELECT everything(t) FROM temp t; -- using type waypoints
SELECT everything(t::waypoint) FROM (TABLE temp) t; -- using type waypoint
Asides:
- There are no "arguments" in the table, but columns.
-
You are not passing
table parameter to this function
, but rather the string value. The way you pass the table by name:You cannot "pass the entire table" as a parameter directly to Postgres, there are no table variables. You would use a cursor or temp table for this.
Function
Your function has an invalid type declaration and is unnecessarily complicated. I seriously doubt you want to create a view:
CREATE FUNCTION everything(_wp waypoint) -- or use type waypoints
RETURNS TABLE(node int, xy text[]) AS
$func$
BEGIN
RETURN QUERY
SELECT ...
END
$func$ LANGUAGE plpgsql;
text array
invalid syntax, using text[]
instead to declare an array text
.
Rather, don't use the table / type waypoints
name as the function parameter name, which would lead to confusing errors.
Or just use a simple SQL function if your case is simple as shown:
CREATE FUNCTION everything(_wp waypoint) -- or use type waypoints
RETURNS TABLE(node int, xy text[]) AS
$func$
SELECT ...
$func$ LANGUAGE sql;
Do not include the language name. This is an identifier.
source to share
If all types are in waypoint
and temp
convertible to text
, you can serialize and deserialize from text
:
SELECT everything(temp::text::waypoint)
FROM temp
However, an explicit construct would be cleaner:
SELECT everything((col1, col2, col3, ...)::waypoint)
FROM temp
or create 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;
source to share