oracle %rowtype equivalent in postgresql

Question

Need to convert the %rowtype of oracle to equivalent in postgresql.

My try:

create table public.test 
(
    id int,
    name varchar(20)
);


insert into test values (1,'A');
insert into test values (2,'B');
insert into test values (3,'C');

Note: I have a variable declare with table %rowtype using which we are checking multiple different column condition's as shown below in the example. It's not working in postgres.

do 
$$
declare pky public.test%rowtype;
begin
    if pky.id=1
    then 
        raise info 'id:1';
    elsif pky.name = 'B'
    then
        raise info 'name:B';
    else
        raise info 'false';
    end if;
end;
$$;

pky is a input parameter of function in actual code.

score 1 · Answer 1 · answered Aug 16 '22 at 13:00

1

The input parameter can be declared using the table name

CREATE OR REPLACE FUNCTION testfn(pky public.test) 
...

For a column type, use %type

CREATE OR REPLACE FUNCTION testfn(pky_id public.test.id%TYPE) 
...

answered Aug 16 '22 at 13:00

JGH

15,928
4
31
48

score 1 · Accepted Answer · answered Aug 16 '22 at 13:10

The variable is correctly declared, but you have not assigned any value to it. You can do this with a simple assignment statement

do 
$$
declare pky public.test;
begin
    pky:= '(11,something)'::public.test;
    raise info '%', pky;
end;
$$;

INFO:  (11,something)

or in a query with into

do 
$$
declare pky public.test;
begin
    select *
    into pky
    from public.test
    where id = 1;
    raise info '%', pky;
end;
$$;

INFO:  (1,A)

or use it as a loop variable

do 
$$
declare pky public.test;
begin
    for pky in
        select *
        from public.test
    loop
        raise info '%', pky;
    end loop;
end;
$$;

INFO:  (1,A)
INFO:  (2,B)
INFO:  (3,C)

As you can see, the %rowtype is not necessary.

oracle %rowtype equivalent in postgresql

2 Answers2