0

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.