Postgres使用记录类型函数创建视图 [英] Postgres Create View With Record Type Function

问题描述

我有一个用户定义的函数 conta_relatos（）作为select语句运行得非常好。但是，当我尝试使用相同的说明创建视图时，它不起作用。 Postgres告诉我列conta_relatos有伪类型的记录。这个函数 conta_relatos（）返回一个记录类型变量。

_{由编辑器添加：}

返回类型是前面问题中定义的众所周知的组合类型：

Postgres函数结束循环并返回错误

以下是查询：

  CREATE OR REPLACE VIEWSumarioAS 
 SELECTAspectos。IDASAspecto Normativo ID，
Aspectos。AspectoAS Aspecto Normativo，
Fatores，IDAS，Fator Normativo ID，
Fatores，FatorASFator Normativo，Diagnostico，Vinculo_Final，
Fatores_1。IDASFator Determinativo ID，
Fatores_1。FatorASFator Determinativo，
Aspectos_1。IDASAspecto Determinativo ID， 
Aspectos_1。AspectoASAspecto Determinativo，
 count（Itens。ID）ASNo Itens，
 conta _relatos（Fatores。ID，Fatores_1。ID）
 FROM 
Diagnostico
 JOIN（AspectosAspectos_1
 JOIN（Fontes 
 JOINItensONFontes。ID=Itens。Fonte
 JOIN（FatoresFatores_1
 JOIN（Aspectos
 JOIN（Vinculos
加入FatoresONVinculos。Fator_Normativo=Fatores。ID）ONAspectos。ID=Fatores。AspectoANDAspectos ID=Fatores。Aspecto）ONFatores_1。ID=Vinculos。Fator_Determinativo）ONItens。ID=Vinculos。Item）ONAspectos_1 。ID=Fatores_1。Aspecto）ONDiagnostico。ID=Vinculos。Diagnostico_ID
 GROUP BYAspectos。ID，Aspectos。Aspecto Fatores，ID，Fatores，Fator，Diagnostico，Vinculo_Final，Fatores_1，ID，Fatores_1，Fator，Aspectos_1，ID Aspectos_1。Aspecto
 ORDER BYAspectos。ID，Aspectos_1。ID，Fatores，Fator，Fatores_1。 
  

解决方案

仔细检查：您只需拆分复合返回类型如下：

pre $ $ codeREATE OR REPLACE VIEWSumarioAS
SELECT ...
< b>（ conta_relatos（Fatores，ID，Fatores_1。ID））。*
FROM ...

有关在手册中访问复合类型的详细信息。

另外：我建议不要在您的JOIN中使用括号，除非您知道正是你在做什么。你拥有它的方式，你迫使一个特定的执行计划。很可能，这不是最好的。

---

第一种方法误解了错误信息

使用 RETURNS记录（我尽可能避免使用它）定义函数时，必须为每个调用提供一个列定义列表，如：

  SELECT * FROM conta_relatos（1,2）AS f（col1 int，col2 text，...）
  

我引用这里的手册：

lockquote

如果函数被定义为返回记录数据类型
，然后是别名或关键字AS必须存在，然后返回格式为（column_name data_type [，...]）的列
定义列表。
列定义列表必须匹配函数返回的实际数量和
列的类型。

干净的解决方案是改变你的函数返回一个已知类型而不是匿名记录。根据具体情况，有多种方式可以解决这个问题。如果您在重写函数时遇到问题，请打开另一个问题。

I have this complex query with a user defined function conta_relatos() running very well as a select statement. But it doesn't work when I try to create a view with the same instructions. Postgres is telling me that the column "conta_relatos" has pseudo-type record. This function, conta_relatos() returns a record type variable.
_{Addition by Editor:}
The return type is a well known composite type as defined in the preceding question:
Postgres Function End Loop and return Error

Below is the query:

CREATE OR REPLACE VIEW "Sumario" AS 
SELECT "Aspectos"."ID" AS "Aspecto Normativo ID", 
    "Aspectos"."Aspecto" AS "Aspecto Normativo", 
    "Fatores"."ID" AS "Fator Normativo ID", 
    "Fatores"."Fator" AS "Fator Normativo", "Diagnostico"."Vinculo_Final", 
    "Fatores_1"."ID" AS "Fator Determinativo ID", 
    "Fatores_1"."Fator" AS "Fator Determinativo", 
    "Aspectos_1"."ID" AS "Aspecto Determinativo ID", 
    "Aspectos_1"."Aspecto" AS "Aspecto Determinativo", 
    count("Itens"."ID") AS "No Itens", 
    conta_relatos("Fatores"."ID", "Fatores_1"."ID")
FROM  
    "Diagnostico"
JOIN ("Aspectos" "Aspectos_1"
JOIN ("Fontes"
JOIN "Itens" ON "Fontes"."ID" = "Itens"."Fonte"
JOIN ("Fatores" "Fatores_1"
JOIN ("Aspectos"
JOIN ("Vinculos"
JOIN "Fatores" ON "Vinculos"."Fator_Normativo" = "Fatores"."ID") ON "Aspectos"."ID" = "Fatores"."Aspecto" AND "Aspectos"."ID" = "Fatores"."Aspecto") ON "Fatores_1"."ID" = "Vinculos"."Fator_Determinativo") ON "Itens"."ID" = "Vinculos"."Item") ON "Aspectos_1"."ID" = "Fatores_1"."Aspecto") ON "Diagnostico"."ID" = "Vinculos"."Diagnostico_ID"
GROUP BY "Aspectos"."ID", "Aspectos"."Aspecto", "Fatores"."ID", "Fatores"."Fator", "Diagnostico"."Vinculo_Final", "Fatores_1"."ID", "Fatores_1"."Fator", "Aspectos_1"."ID", "Aspectos_1"."Aspecto"
ORDER BY "Aspectos"."ID", "Aspectos_1"."ID", "Fatores"."Fator", "Fatores_1"."Fator";

解决方案

On closer inspection: You only need to split the composite return type like this:

CREATE OR REPLACE VIEW "Sumario" AS 
SELECT ...
 (conta_relatos("Fatores"."ID", "Fatores_1"."ID")).*
FROM ...

Details about accessing composite types in the manual.

As an aside: I would advise not to use parenthesis for your JOINs unless you know exactly what you are doing. The way you have it, you are forcing one particular execution plan. Chances are, it's not the best one.

---

First approach misinterpreted the error message

When you define a function with RETURNS record (which I avoid when possible), you have to provide a column definition list with every call, like:

SELECT * FROM conta_relatos(1,2) AS f(col1 int, col2 text, ...)

I quote the manual here:

If the function has been defined as returning the record data type, then an alias or the key word AS must be present, followed by a column definition list in the form ( column_name data_type [, ... ] ). The column definition list must match the actual number and types of columns returned by the function.

The clean solution is to change your function to return a well known type instead of an anonymous record. There are various ways to go about that, depending on the circumstances. If you have trouble rewriting your function, open another question.

这篇关于Postgres使用记录类型函数创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！