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.
LinkedIn
Twitter
