Oracle to PostgreSQL – How to Create Type Object

oraclepostgresql

In Oracle:

create or replace type tr_del as object (row_id varchar2(18), pk_id number);

select tr_del(last_name, employee_id) 
from employees where 
department_id=30;

output:

(Raphaely, 114)  
(Khoo, 115)  
(Baida, 116)

How can I achieve the same in PostgreSQL?

Best Answer

Although the syntax is different, you can achieve exactly what you want.

You can create type by using the script below; 

CREATE TYPE tr_del AS (row_id varchar(18), pk_id integer);

Then your table structure should look like this; 

CREATE TABLE employees (
   department_id integer,
   any_field_name tr_del,
   ...
);

After that you can use it in the query like this;

SELECT
    (any_field_name).row_id AS last_name,
    (any_field_name).pk_id AS employee_id
FROM
    employees
WHERE
    department_id = 30;

Fiddle is here

You may want to read Composite Types section.

Related Question