Oracle row type vs PostgreSQL record

The Oracle row type concept can be accomplished with the record type in PostgreSQL.

Example:

Oracle:

CURSOR travel_cur IS 
SELECT  c.travel_id FROM TRAVEL_CON c, TRAVEL_PAT p
                                WHERE c.travel_id = p.travel_id AND p.pat_id = source;
travel_rec travel_cur%ROWTYPE;         <---- here travel_rec holds multiple records at a time.
BEGIN
OPEN travel_cur; 
    LOOP 
        FETCH travel_cur INTO travel_rec; 
        EXIT WHEN travel_cur%NOTFOUND; 
        sql_stmt := 'delete from TRAVEL_PAT where travel_id = :1'; 
        EXECUTE IMMEDIATE sql_stmt USING travel_rec.travel_id;
    END LOOP; 
CLOSE travel_cur;
END;

PostgreSQL:

DECLARE
    sql_stmt varchar(1000);
    travel_rec record;            <---- here travel_rec holds only a single record at a time.
BEGIN
    FOR travel_rec  in SELECT  c.travel_id FROM TRAVEL_CON c, TRAVEL_PAT p
                                WHERE c.travel_id = p.travel_id AND p.pat_id = source;
    LOOP
            sql_stmt := 'delete from TRAVEL_PAT where travel_id = :1'; 
    EXECUTE IMMEDIATE sql_stmt USING travel_rec.travel_id;
    END LOOP; 
END;

PostgreSQL - Find all object types in the schema

equivalent of oracle user_objects

select 
    nsp.nspname as SchemaName
    ,cls.relname as ObjectName 
    ,rol.rolname as ObjectOwner
    ,case cls.relkind
        when 'r' then 'TABLE'
        when 'm' then 'MATERIALIZED_VIEW'
        when 'i' then 'INDEX'
        when 'S' then 'SEQUENCE'
        when 'v' then 'VIEW'
        when 'c' then 'TYPE'
        else cls.relkind::text
    end as ObjectType
from pg_class cls
join pg_roles rol 
on rol.oid = cls.relowner
join pg_namespace nsp 
on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
    and nsp.nspname not like 'pg_toast%'
    and rol.rolname = 'schema_name' and cls.relname='object_name' 
order by nsp.nspname, cls.relname

Postgres - uuid_generate_v4() does not exists

 

We might encounter this issue, when we try to create a Materialized View in Postgresql. For example In this case we have an Oracle MV with SYS_GUID() and I am trying to create an equivalent Postgres MV.

 Oracle:

CREATE MATERIALIZED VIEW "PROD"."MY_MVIEWS" ("ID")

AS

    SELECT TO_NUMBER (SYS_GUID (), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')    AS ID

      FROM MY_TABLE

     WHERE A = B;

PostgreSQL:

CREATE MATERIALIZED VIEW "PROD"."MY_MVIEWS" 

AS

   SELECT TO_NUMBER(uuid_generate_v4(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') AS ID

 FROM MY_TABLE

     WHERE A = B;

Error:

ERROR:  function to_number(uuid, unknown) does not exist
LINE 2: SELECT TO_NUMBER(uuid_generate_v4(),'XXXXXXXXXXXXXXXXXXXXXXX...
               ^
HINT:  No function matches the given name and argument types. 

You might need to add explicit type casts.
SQL state: 42883
Character: 60

Solution: 

1. Try to create "uuid-ossp" extention:

   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

2. In my case, it did not work and I was asked to install the extension. 

So I  am going to install the extension first.

[root@dbserver]# yum install postgresql15-contrib

:

Installed:
  postgresql15-contrib.x86_64 0:15.2-1PGDG.rhel7

Complete!

3. Now, create the extension as the owner of the database, in my case postgres.

bash-4.2$ psql pkprod -U postgres
psql (15.2)
Type "help" for help.

prod1=#
prod1=# select current_user;
 current_user
--------------
 postgres
(1 row)

prod1=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
prod1=#

->> Grant privileges for the user schema to access it. 

Now, test if the function works....

select uuid_generate_v4();

uuid

88ea95a2-b289-440e-94fd-547555665287

Here it outputs with characters. But what we need is numeric data type. 

So perform the below type cast.

select TO_NUMBER(cast (uuid_generate_v4() as text),'99999999999999999999'); 

numeric:
164387628487