Question 115133 in PostgreSQL Question posted in
The official documentation can be found here.

oracle %rowtype equivalent in postgresql

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.

Tags:

2

Answers


  1. 0

    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) 
...
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search