Leketo Leketo - 2 months ago 17
SQL Question

return %ROWTYPE from function PostgreSQL

I've tried to do following:

CREATE TABLE mytable(id integer NOT NULL,
name character varying,
CONSTRAINT pk_table PRIMARY KEY (id));





CREATE OR REPLACE FUNCTION fnmytable(inout p_rec mytable)
RETURNS mytable AS
$BODY$
declare
begin
p_rec.id := 1;--sequence
INSERT INTO mytable(id,
name)
VALUES (p_rec.id,
p_rec.name);
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;





do
$$
declare
r_rec mytable%rowtype;
begin
r_rec.name := 'Jorge';
perform fnmytable(r_rec);
raise notice 'OUT ID: %', r_rec.id;
end;
$$



NOTICE: OUT ID: "NULL"


How to return the value of the sequence?

Answer

Please try this

    CREATE OR REPLACE FUNCTION fnmytable(inout p_rec mytable) AS
   --EDITED HERE
    $BODY$
    declare
    begin
    p_rec.id := 1;--sequence 
      INSERT INTO mytable(id, 
                          name)
                  VALUES (p_rec.id, 
                          p_rec.name);
    end;
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;

calling the function

do
$$
declare
  r_rec mytable%rowtype;
begin
 r_rec.name := 'Jorge';
 select * from fnmytable(r_rec) into r_rec;  --EDITED HERE
 raise notice 'OUT ID: %', r_rec.id;
end;
$$
Comments