Hello,
need some help 
I have used ora2pg to migrate packages from Oracle to postgresql.
Anyone please know the equivalent of Oracle data type “%ROWTYPE” in postgresql?
Thanks in advanve.
Mourad.
In PostgreSQL, including its deployment on Google Cloud SQL, there is no direct equivalent to Oracle’s %ROWTYPE attribute, which is used in PL/SQL to declare a variable that can store an entire row of a table or view. PostgreSQL offers a different approach to achieve similar functionality, primarily through the use of composite types and the record
Every table in PostgreSQL implicitly defines a composite type with the same name as the table, which can be used to emulate %ROWTYPE. You can declare a variable of this composite type to hold a row of the table.
– Assuming you have a table named “employee”
CREATE TABLE employee (
id serial PRIMARY KEY,
name text,
salary numeric
);
– You can declare a variable of the composite type “employee”
DO $$
DECLARE
emp_record employee; – This is similar to %ROWTYPE in Oracle
BEGIN
SELECT * INTO emp_record FROM employee WHERE id = 1;
RAISE NOTICE ‘Employee Name: %’, emp_record.name;
END $$;
In addition, PostgreSQL also supports the record type, which is a flexible type that can hold any row-like structure. While record variables are versatile, they require you to define the structure at runtime, typically when you assign a query result to them.
DO $$
DECLARE
emp_record record;
BEGIN
SELECT * INTO emp_record FROM employee WHERE id = 1;
– Note: Accessing fields in a record requires casting
RAISE NOTICE ‘Employee Name: %’, (emp_record).name;
END $$;
While PostgreSQL on Google Cloud SQL does not have a direct %ROWTYPE equivalent, you can achieve similar functionality through composite types or the record type, depending on your specific needs and preferences.
thank a lot @ms4446