Postgres Create View With Record Type Function

Question

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";

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 23, 2013 at 14:45

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Britto Over a year ago

Thank you again Erwin, I'll post another question so i can rewrite my function.

Erwin Brandstetter Over a year ago

@Britto: Welcome. And remember to add your version of Postgres in the next question.

Erwin Brandstetter Over a year ago

@Britto: Updated my answer. I misinterpreted the error message at first.

Collectives™ on Stack Overflow

Postgres Create View With Record Type Function

1 Answer 1

First approach misinterpreted the error message

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

First approach misinterpreted the error message

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related