tipos - transacciones sql

Obtener valores de RETURNING*dentro de una transacción

Question

Cuando tengo dos INSERT SQL (ver a continuación) dentro de un begin; y commit; transacción entonces el RETURNING * no devuelve nada, pero si saco el begin; y commit; el RETURNING * devuelve el registro insertado.
¿Cómo puedo hacer que el RETURNING * funcione dentro de una transacción?

BEGIN;

INSERT INTO gis_field_configuration
   (level_unique_name, level_name_caption, use_for_charts, use_as_displayby, 
    displayby_label, data_type, level_help_text)
VALUES (
    ''[john].[john]'',
    ''john'',
    ''false'',
    ''false'',
    '''',
    ''text'',
    ''help text''
);

INSERT INTO gis_field_configuration_bycube
   (cube, level_unique_name)
VALUES (
    ''Instruments'',
    ''[john].[john]''
) RETURNING *;

COMMIT;

Answer 1

Sospecho que está ejecutando todas estas declaraciones como una cadena de texto único del controlador de cliente de su idioma. Si ese es el caso, el resultado devuelto al cliente será lo que devuelva la última declaración en el bloque de declaraciones que envió. En este caso, esa es la declaración COMMIT , que no tiene ningún resultado, por lo que no obtendrá ningún resultado.

Ejecute cada enunciado uno por uno y debería funcionar bien. No puedo dar un ejemplo más detallado porque no ha mencionado qué idioma está utilizando.

Aquí hay un ejemplo de Python / psycopg. El primer enfoque donde todo el SQL se envía en un blob causa una excepción cuando trato de obtener los resultados, porque commit no produce un resultado. El segundo ejemplo, donde ejecuto cada instrucción por separado y obtengo los resultados de la select antes de commit , funciona bien.

import psycopg2

conn = psycopg2.connect("dbname=regress")
curs = conn.cursor();

# All in one blob
try:
    curs.execute("BEGIN; SELECT generate_series(1,10); COMMIT;")
    print(curs.fetchall())
except (psycopg2.ProgrammingError) as ex:
    print("Failed: ", ex)

# vs one-by-one
curs.execute("BEGIN;")
curs.execute("SELECT x.* FROM generate_series(1,10) x;")
print(curs.fetchall())
curs.execute("COMMIT;")

Salida:

$ python3 test.py 
Failed:  no results to fetch
[(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,), (10,)]

Answer 2

Una forma sería usar un CTE modificador de datos y empaquetar los dos INSERT s en un solo comando. Requiere PostgreSQL 9.1 o posterior:

WITH x AS (
   INSERT INTO gis_field_configuration (level_unique_name, level_name_caption
                ,use_for_charts, use_as_displayby, displayby_label, data_type
                ,level_help_text)
   VALUES (
       ''[john].[john]'',
       ''john'',
       ''false'',
       ''false'',
       '''',
       ''text'',
       ''help text''
   )
   )
INSERT INTO gis_field_configuration_bycube
   (cube, level_unique_name)
VALUES (
    ''Instruments'',
    ''[john].[john]''
    )
RETURNING *;

Sin embargo, recuperas los valores con RETURNING * en cualquier caso. Simplemente COMMIT antes de enviar COMMIT . Enviado como lote, solo se devuelven los resultados del last comando, que sería el resultado de COMMIT , si envía todos los comandos como un solo lote.

Mantenga COMMIT; hasta que haya recibido los resultados del segundo INSERT .

En una función plpgsql

Una función se ejecuta dentro de una transacción automáticamente. No necesita BEGIN / COMMIT explícito. Para reutilizar los valores que obtiene de un INSERT use las RETURNING *expressions* INTO [STRICT] *target* .

Considere esta demostración simple:

CREATE TABLE foo (foo_id serial, bar text);

CREATE OR REPLACE FUNCTION f_foo()
  RETURNS void LANGUAGE plpgsql AS
$BODY$
DECLARE
   foo_var foo; -- type name = table name, fits return type of RETURNING *
   -- or use a generic type record
BEGIN

   INSERT INTO foo (bar)
   VALUES (''baz'')
   RETURNING *
   INTO foo_var;

   RAISE NOTICE ''New id is: %'', foo_var.foo_id;

   -- do stuff with foo_var

   END;
   $BODY$;

SELECT f_foo();