UPDATE a whole row in PL/pgSQL

Question

I have plpgsql function:

CREATE OR REPLACE FUNCTION test() RETURNS VOID AS
$$
DECLARE
    my_row my_table%ROWTYPE;
BEGIN
    SELECT * INTO my_row FROM my_table WHERE id='1';
    my_row.date := now();
END;
$$ LANGUAGE plpgsql;

I would like to know if it's possible to directly UPDATE my_row record.

The only way I've found to do it now is:

UPDATE my_table SET date=now() WHERE id='1';

Note this is only an example function, the real one is far more complex than this.

I'm using PostgreSQL 9.2.

UPDATE:

Sorry for the confusion, what I wanted to say is:

SELECT * INTO my_row FROM my_table INTO my_row WHERE id='1';
make_lots_of_complicated_modifications_to(my_row, other_complex_parameters);
UPDATE my_row;

I.e. Use my_row to persist information in the underlying table. I have lots of parameters to update.

Why don't you select NOW() in the first place, instead of a vague * ? — Frank Heikens, Sep 21 '12 at 11:01
Do you want to update the variable in your plpgsql function or the row in the underlying table? — Erwin Brandstetter, Sep 21 '12 at 11:23
I want to persist the variable into the table without having to rewrite a field-by-field query. — Jeroni, Sep 21 '12 at 14:05

Erwin Brandstetter · Accepted Answer · 2012-09-21T14:44:54.803

I would like to know if it's possible to directly update "my_row" record.

It is.
You can update columns of a row or record type in plpgsql - just like you have it. It should be working, obviously?

This would update the underlying table, of course, not the variable!

UPDATE my_table SET date=now() WHERE id='1';

You are confusing two things here ...

Answer to clarification in comment

I don't think there is syntax in PostgreSQL that can UPDATE a whole row. You can UPDATE a column list, though. Consider this demo:

Note how I use thedate instead of date as column name, date is a reserved word in every SQL standard and a type name in PostgreSQL.

CREATE TEMP TABLE my_table (id serial, thedate date);
INSERT INTO my_table(thedate) VALUES (now());

CREATE OR REPLACE FUNCTION test_up()
  RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
    _r my_table;
BEGIN
   SELECT * INTO _r FROM my_table WHERE id = 1;
   _r.thedate := now()::date + 5 ;

   UPDATE my_table t
    -- explicit list of columns to be to updated
   SET   (id, thedate) = (_r.id, _r.thedate)
   WHERE  t.id = 1;
END
$func$;

SELECT test_up();
SELECT * FROM my_table;

However, you can INSERT a whole row easily. Just don't supply a column list for the table (which you normally should, but in this case it is perfectly ok, not to).

As an UPDATE is internally a DELETE followed by an INSERT anyway, and a function automatically encapsulates everything in a transaction, I don't see, why you couldn't use this instead:

CREATE OR REPLACE FUNCTION x.test_ delins()
  RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
    _r my_table;
BEGIN
   SELECT * INTO _r
   FROM my_table WHERE id = 1;
   _r.thedate := now()::date + 10;

   DELETE FROM my_table t WHERE t.id = 1;
   INSERT INTO my_table SELECT _r.*;
END
$func$;

Do you want to `UPDATE` the whole row or just the one column? — Erwin Brandstetter, Sep 21 '12 at 14:06
The whole row, I'm changing lots of fields with complex calculations. It would be something like oracle's SET ROW. — Jeroni, Sep 21 '12 at 14:08
Erwin... it seems like the problem with the delete/insert method would be issues with foreign keys. How would/could you over come those issues? — David S, Oct 17 '12 at 19:02
@DavidS: Foreign keys pointing to the table would prohibit the DELETE/INSERT workaround. You *could* use dynamic SQL to generate a long list of columns for the `UPDATE` from the system catalog, in particular [`pg_attribute`](http://www.postgresql.org/docs/current/interactive/catalog-pg-attribute.html) ... — Erwin Brandstetter, Oct 18 '12 at 06:12
@ErwinBrandstetter I was playing around with this more this morning because I have a similar need right now. Changing the foreign key to be deferrable and then calling "SET CONSTRAINTS somename_fkey DEFERRED;" at the beginning of the procedure seems to work for me. I might post my code and see if anyone can suggest a better solution. — David S, Oct 18 '12 at 15:55

score 3 · Answer 2 · answered Apr 10 '20 at 14:40

I managed to get this working in PLPGSQL in a couple of lines of code.

Given a table called table in a schema called example, and a record of the same type declared as _record, you can update all the columns in the table to match the record using the following hack:

declare _record example.table;

...

-- get the columns in the correct order, as a string
select string_agg(format('%I', column_name), ',' order by ordinal_position)
  into _columns
  from information_schema.columns
  where table_schema='example' and table_name='table';

execute 'update example.table set (' || _columns || ') = row($1.*) where pkey=$2'
  using _record, _record.pkey;

In the above example, of course, _record.pkey is the table's primary key.

score 1 · Answer 3 · answered Dec 05 '21 at 16:23

1

Postgresql has not set row in update. If you wont update full row you should assign value for each column separately

answered Dec 05 '21 at 16:23

Alisherbek

27
3

solaimuruganv · Answer 4 · 2012-09-21T11:37:12.263

0

yes, its possible to update / append the row-type variable,

CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$
DECLARE    
         my_row my_table%ROWTYPE;
BEGIN
         SELECT * INTO my_row FROM my_table WHERE id='1';
         my_row.date := now(); 
         raise notice  'date  : %; ',my_row.date;
END;
$$ LANGUAGE plpgsql;

here the raise notice will display the today's date only.

but this will not update the column date in my_table.

edited Sep 21 '12 at 11:37

answered Sep 21 '12 at 11:20

solaimuruganv

27,177
1
18
23

I know, the date was an example, I'm making lots of changes into my_row and I want an easy way to persist the information again without having to write an UPDATE my_table SET WHERE id=my_row.id. I think Oracle has a SET ROW system to persist records with these structures. – Jeroni Sep 21 '12 at 14:18

UPDATE a whole row in PL/pgSQL

4 Answers4

Answer to clarification in comment

Linked