Use pseudo-types to create materialized view in PostgreSQL-CodePudding

I'd like to create a materialized view with records as columns, which are pseudo-types and according to the PostgreSQL-Documentation those types are not allowed to be used as column types. So no wonder I get an error when trying to do it anyways. Is there some way around?

To give an example:

CREATE MATERIALIZED VIEW data AS 
   SELECT emp_addr, salaries 
   FROM (
      SELECT employees.id as id, first_name, last_name, city, street, housenumber FROM employees 
      INNER JOIN addresses
      ON employees.id = addresses.employee_id
   ) emp_addr
   INNER JOIN salaries
   ON emp_addr.id = salaries.employee_id;

The problem here is emp_addr which is a record type (pseudo-type).

This on the other hand would be no problem:

CREATE MATERIALIZED VIEW data AS 
   SELECT employees, addresses, salaries 
   FROM employees 
   INNER JOIN addresses
   ON employees.id = addresses.employee_id
   INNER JOIN salaries
   ON employees.id = salaries.employee_id;

No pseudo-types here. But what if I want the first example to work? Can I somehow convert the pseudo-type emp_addr to a type that is allowed to create a materialized view, so that I can afterwards query like this for example:

SELECT emp_addr, salaries FROM data;

SELECT (emp_addr).*, (salaries).* FROM data;

Im looking forward for your advice...

CodePudding user response：

Not entirely sure what you are talking about as, in your examples, emp_addr is not some sort of pseudo-type, it’s a table name. Just use

SELECT emp_addr.*, salaries.*

However, you need to bear in mind 2 things:

This will only work as long as the same column name doesn’t appear in both tables. If this were the case you’d have duplicate column names in your MV, which is obviously not allowed
While you are not explicitly listing the column names for each source table, when you run the statement it will expand this out. If you subsequently added a column to the salaries table, for example, this would not automatically get reflected in your MV. You’d have to re-create the MV to pick up the new column

Just as an FYI, using tablename.* in a view or MV is really bad practice, you should always explicitly list out the individual columns.

CodePudding user response：

Found a solution, which is pretty straightforward thinking about it now. Just had to cast the pseudo-type to an actual type:

CREATE TYPE emp_addr_type AS (
   id INT,
   first_name VARCHAR(255),
   last_name VARCHAR(255),
   city VARCHAR(255),
   street VARCHAR(255),
   housenumber VARCHAR(20)
);

CREATE MATERIALIZED VIEW data AS 
   SELECT emp_addr::emp_addr_type, salaries 
   FROM (
      SELECT employees.id as id, first_name, last_name, city, street, housenumber FROM employees 
      INNER JOIN addresses
      ON employees.id = addresses.employee_id
   ) emp_addr
   INNER JOIN salaries
   ON emp_addr.id = salaries.employee_id;