Devolver identificación si existe una fila, INSERTAR de lo contrario

Estoy escribiendo una function en node.js para consultar una tabla PostgreSQL.
Si la fila existe, quiero devolver la columna de identificación de la fila.
Si no existe, quiero insertlo y devolver la identificación ( insert into ... returning id ).

He estado probando variaciones de case y if else y parece que no puedo hacer que funcione.

Sugeriría hacer la verificación en el lado de la database y simplemente devolver el id a nodejs.

Ejemplo:

 CREATE OR REPLACE FUNCTION foo(p_param1 tableFoo.attr1%TYPE, p_param2 tableFoo.attr1%TYPE) RETURNS tableFoo.id%TYPE AS $$ DECLARE v_id tableFoo.pk%TYPE; BEGIN SELECT id INTO v_id FROM tableFoo WHERE attr1 = p_param1 AND attr2 = p_param2; IF v_id IS NULL THEN INSERT INTO tableFoo(id, attr1, attr2) VALUES (DEFAULT, p_param1, p_param2) RETURNING id INTO v_id; END IF; RETURN v_id: END; $$ LANGUAGE plpgsql; 

Y que en el Node.js-side (estoy usando node-postgres en este ejemplo):

 var pg = require('pg'); pg.connect('someConnectionString', function(connErr, client){ //do some errorchecking here client.query('SELECT id FROM foo($1, $2);', ['foo', 'bar'], function(queryErr, result){ //errorchecking var id = result.rows[0].id; }; }); 

Una solución en una sola statement de SQL. Requiere PostgreSQL 8.4 o posterior sin embargo.
Considere la siguiente demostración:

Configuración de testing:

 CREATE TEMP TABLE tbl ( id serial PRIMARY KEY ,txt text UNIQUE -- obviously there is unique column (or set of columns) ); INSERT INTO tbl(txt) VALUES ('one'), ('two'); 

Comando INSERT / SELECT:

 WITH v AS (SELECT 'three'::text AS txt) ,s AS (SELECT id FROM tbl JOIN v USING (txt)) ,i AS ( INSERT INTO tbl (txt) SELECT txt FROM v WHERE NOT EXISTS (SELECT * FROM s) RETURNING id ) SELECT id, 'i'::text AS src FROM i UNION ALL SELECT id, 's' FROM s; 
  • El primer CTE v no es estrictamente necesario, pero logra que solo tenga que ingresar sus valores una vez.

  • El segundo CTE s selecciona la id de tbl si existe la "fila".

  • El tercer CTE i inserta la "fila" en tbl si (y solo si) no existe, devolviendo id .

  • El SELECT final devuelve el id . Agregué un src columna que indica la "fuente" – si la "fila" preexistió y la id proviene de un SELECCIONAR, o la "fila" era nueva y también lo es la id .

  • Esta versión debe ser lo más rápida posible, ya que no necesita un SELECTO adicional de tbl y utiliza los CTE en su lugar.

Para que esto sea seguro contra las posibles condiciones de carrera en un entorno multiusuario:
También para las técnicas actualizadas que usan el nuevo UPSERT en Postgres 9.5 o posterior:

  • ¿SELECCIONAR o INSERTAR en una function es propenso a las condiciones de carrera?

Algo como esto, si estás en PostgreSQL 9.1

 with test_insert as ( insert into foo (id, col1, col2) select 42, 'Foo', 'Bar' where not exists (select * from foo where id = 42) returning foo.id, foo.col1, foo.col2 ) select id, col1, col2 from test_insert union select id, col1, col2 from foo where id = 42; 

Es un poco largo y necesita repetir la identificación para probar varias veces, pero no puedo pensar en una solución diferente que implique una sola instrucción SQL.

Si existe una fila con id=42 , el CTE escribible no insertá nada y, por lo tanto, la segunda parte de unión devolverá la fila existente.

Al probar esto, en realidad pensé que la nueva fila sería devuelta dos veces (por lo tanto, una union no es una union all ) pero resulta que el resultado de la segunda statement seleccionada se evalúa antes de ejecutar toda la instrucción y no se ve la nueva fila insertada Entonces, en caso de que se inserte una nueva fila, se tomará de la parte de "retorno".

 create table t ( id serial primary key, a integer ) ; insert into t (a) select 2 from ( select count(*) as s from t where a = 2 ) s where ss = 0 ; select id from t where a = 2 ;