Geek Answers Handbook

UPDATING THE WHOLE Row IN PL / pgSQL

I have a 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 a record can be updated directly my_row.

The only way I found this now is:

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

Please note that this is only an example function, the real one is much more complicated than this.

I am using PostgreSQL 9.2.

UPDATE:

Sorry for the confusion, I wanted to say:

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;

those. Use my_ rowto store information in the base table. I have many options for updating.

+3
source share
2 answers

I would like to know if it is possible to directly update the "my_row" record.

.
plpgsql - , . , ?

, , !

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

...

, PostgreSQL , UPDATE . UPDATE . :

, thedate date , date SQL 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;

INSERT . ( , , ).

UPDATE , INSERT, , , :

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$;
+4

, / ,

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;

.

my_table.

0

More articles:


All Articles