如何将表格参数传递给此函数? [英] 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;
我有一个表，该表具有以waypoint数据类型的结构形式组织的参数.该表本身不是明确的waypoint类型.
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);
或select everything(select * from temp);
但是它说select或附近的语法错误，而waypoints列对于前者不存在.
But it says syntax error at or near select for the latter and column waypoints does not exist for the former.
我该如何进行?
推荐答案
在Postgres 9.4 中进行的所有测试.
Everything tested in Postgres 9.4.
Postgres在处理ROW类型的语法上有一些弱点.您不能直接从表(别名)进行投射:
Postgres has some weak spots in the syntax for handling ROW types. You cannot cast from a table (alias) directly:
SELECT w::waypoint FROM waypoints w;
ERROR: cannot cast type waypoints to waypoint
解决方案仅一步之遥:分解子查询中的行，然后进行转换.这样，列值将被分解并直接包装为新类型，而无需转换为text并返回.无需单独列出所有列，也无需创建自定义强制转换:
The solution is only one step away: decompose the row in a subquery, then the cast works. This way, column values are decomposed and wrapped into the new type directly, without casting to text and back. No need to list all columns individually and you don't need to create a custom cast, either:
SELECT (w.*)::waypoint FROM (SELECT * FROM waypoints) w;
或更短:
SELECT w.*::waypoint FROM (TABLE waypoints) w;
或更短:
SELECT w::waypoint FROM (TABLE waypoints) w;
- 是否存在用于SELECT * FROM的快捷方式?
- Is there a shortcut for SELECT * FROM?
在进行3万行行和简单类型的快速测试时，它更短，更快捷，快10倍，而不是强制转换为text并返回.如果您有(大)jsonb列或任何复杂类型(到/c text的昂贵转换)，两者之间的差异将会更大.
That's shorter and faster, in a quick test with 30k rows and simple types 10x faster than casting to text and back. If you have (big) jsonb columns or any complex type (expensive conversion to/from text), the difference will be much bigger, yet.
更重要的是，您不需要另一种自定义复合(ROW)类型.每个表已经自动将其行定义为类型.只需使用现有的类型waypoints而不是waypoint(如果可能).然后，您只需要:
More importantly, you don't need another custom composite (ROW) 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;
或者，例如:
SELECT everything(t) FROM temp t; -- using type waypoints
SELECT everything(t::waypoint) FROM (TABLE temp) t; -- using type waypoint
除:
- 表没有参数"，但有列.
-
您不是传递
table parameter to this function，而是传递行值.这就是通过名称传递表的方式:
- A table does not have "arguments" but columns.
You are not passing a
table parameter to this function, but rather a row value. That's how you pass a table by name:
您不能直接在Postgres中传递整个表"作为参数，因为没有表变量.您将为此使用游标或临时表.
You can't "pass a whole table" as parameter directly in Postgres, there are not table variables. You would use a cursor or a temp table for that.
您的函数具有无效的类型声明，并且不必要地复杂.我严重怀疑您要创建一个视图:
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[]) AS
$func$
BEGIN
RETURN QUERY
SELECT ...
END
$func$ LANGUAGE plpgsql;
text array是无效的语法，请使用text[]来声明text的数组.
text array is not valid syntax, using text[] instead to declare an array of text.
不要将表/类型名称waypoints用作函数参数名称，这会使您容易陷入混乱.
Rather not use the table / type name waypoints as function parameter name, that opens you up to confusing errors.
或者，如果您的情况像演示的那样简单，则只需使用简单的SQL函数:
Or just use a simple SQL function if your case is as simple as demonstrated:
CREATE FUNCTION everything(_wp waypoint) -- or use type waypoints
RETURNS TABLE(node int, xy text[]) AS
$func$
SELECT ...
$func$ LANGUAGE sql;
不引用语言名称.这是一个标识符.
Don't quote the language name. It's an identifier.
这篇关于如何将表格参数传递给此函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
