POSTGRESQL - FUNCTION SELECT + UPDATE

Question

I need execute update for each return of the select, but I don't know how I can do it.

In firebird I have this code:

 BEGIN
  FOR
SELECT data_cadastro || ' ' || hora_cadastro  as data_hora_cadastro,codigo_alteracao_convenio  
  FROM CC_ALTERACAO_CONVENIO 
  INTO:data_hora_cadastro,codigo_alteracao_convenio

  DO
  BEGIN
    update CC_ALTERACAO_CONVENIO 
       set data_hora_cadastro = :data_hora_cadastro
     where codigo_alteracao_convenio = :codigo_alteracao_convenio;
  suspend;
  END
END

I want change to function in postgresql.

I tried this, but not work because I don't know the syntax of postgresql of how can I do it.

CREATE OR REPLACE FUNCTION sim.ajuste_alteracao_convenio(OUT data_hora_cadastro character varying, OUT codigo_alteracao_convenio integer)
      RETURNS SETOF record AS
    $BODY$
    DECLARE
       v_data_hora_cadastro character varying;
       v_codigo_alteracao_convenio INTEGER;
      BEGIN
        RETURN QUERY 
            SELECT data_cadastro || ' ' || hora_cadastro  as data_hora_cadastro,codigo_alteracao_convenio
            FROM sim.CC_ALTERACAO_CONVENIO 


        --loop
        BEGIN
            update sim.CC_ALTERACAO_CONVENIO 
            set data_hora_cadastro = v_data_hora_cadastro
            where codigo_alteracao_convenio = v_codigo_alteracao_convenio;

        END
         --END LOOP;
      END;      
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100
      ROWS 1000;

Could someone give me a direction of how can I solved this?

SOLVED

create type foo as (
data_hora_cadastro timestamp, 
codigo_alteracao_convenio integer
)


CREATE OR REPLACE FUNCTION sim.ajuste_alteracao_convenio3() 
RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT data_cadastro || ' ' || hora_cadastro  as data_hora_cadastro,codigo_alteracao_convenio FROM sim.CC_ALTERACAO_CONVENIO 
    LOOP
        update sim.CC_ALTERACAO_CONVENIO 
        set data_hora_cadastro = r.data_hora_cadastro
        where codigo_alteracao_convenio = r.codigo_alteracao_convenio;
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

Thank you all

IIUC, you have separate fields for {date,time}, and you want to update a {timestamp} column from the combination of these. In that case: you don't need a cursor or loop, and you won't need a function. — joop, Aug 16 '16 at 15:10
@joop Thanks for answering. I need create a new function for update and execute this in my function? — Leonardo Lacerda, Aug 16 '16 at 16:25
This can be much simpler. Is `codigo_alteracao_convenio` a `UNIQUE` column? If you provide your version of Postgres and the actual table definition (like you *should*), everything is clear. — Erwin Brandstetter, Aug 18 '16 at 02:57

score 0 · Answer 1 · edited May 23 '17 at 11:51

Information is missing in the question, but it looks like all you need is a simple UPDATE with RETURNING:

UPDATE sim.cc_alteracao_convenio a
SET    data_hora_cadastro = a.data_cadastro + a.hora_cadastro
RETURNING a.data_hora_cadastro, a.codigo_alteracao_convenio;

Assuming data_cadastro is data type date and hora_cadastro is data type time. Currently, you convert both to text, concatenate and cast back to timestamp. That's much more expensive than it needs to be. Just add both together: data_cadastro + hora_cadastro

The UPDATE itself looks like you are storing functionally dependent values redundantly. Once you've updated data_hora_cadastro you can drop data_cadastro and hora_cadastrocan.

If you positively need a function:

CREATE OR REPLACE FUNCTION sim.ajuste_alteracao_convenio3() 
  RETURNS TABLE (data_hora_cadastro        timestamp
               , codigo_alteracao_convenio integer) AS
$func$
   UPDATE sim.cc_alteracao_convenio a
   SET    data_hora_cadastro = a.data_cadastro + a.hora_cadastro
   RETURNING a.data_hora_cadastro, a.codigo_alteracao_convenio;
$func$  LANGUAGE sql;  -- never quote the language name

You don't need to create a composite type, just use RETURNS TABLE() instead.

Or, if you need pre-UPDATE values:

Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

score -1 · Answer 2 · answered Aug 16 '16 at 15:11

-1

this is what I do, it's an idea but may be it inspires you:

WITH insusu AS (
    SELECT data_cadastro || ' ' || hora_cadastro  
        as data_hora_cadastro,codigo_alteracao_convenio
    FROM sim.CC_ALTERACAO_CONVENIO 
    RETURNING id
)   
update sim.CC_ALTERACAO_CONVENIO 
        set data_hora_cadastro =:data_hora_cadastro
from insusu;

select *
from insusu;

The Objetive is uses a with to determine with what data need work.

answered Aug 16 '16 at 15:11

Frank N Stein

2,219
3
22
26

Thanks for answering. In my case, I don't have "ID" column, when I execute this code, this error pops up: **ERROR: syntax error at or near "RETURNING" ** – Leonardo Lacerda Aug 16 '16 at 16:33
I tried RETURNING * but not work. The same error message pops up. – Leonardo Lacerda Aug 16 '16 at 16:54
In the "Returning" you can put a list of fields as ouput ej: id, date, other... the fields that you need to identify the row. This query is schematic, you will need to adapt to your tables and needs. – Frank N Stein Aug 17 '16 at 15:31
A CTE doesn't work like that. And `SELECT` has no `RETURNING` clause. And none of this is needed in the first place. – Erwin Brandstetter Aug 18 '16 at 03:19

POSTGRESQL - FUNCTION SELECT + UPDATE

2 Answers2