如果存在一行，则返回ID，否则返回INSERT [英] Return id if a row exists, INSERT otherwise

问题描述

我正在node.js中编写一个函数来查询PostgreSQL表。

如果该行存在，我想从该行返回id列。

如果它不存在，我想将其插入并返回ID（插入到...返回ID ）。

I'm writing a function in node.js to query a PostgreSQL table.
If the row exists, I want to return the id column from the row.
If it doesn't exist, I want to insert it and return the id (insert into ... returning id).

我一直在尝试 case 和 if else 语句的变体，但似乎无法理解

I've been trying variations of case and if else statements and can't seem to get it to work.

推荐答案

我建议在数据库端进行检查，并将ID返回给nodejs。

I would suggest doing the checking on the database side and just returning the id to nodejs.

示例：

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;

比在Node.js端（在此示例中，我使用的是node-postgres） ：

And than on the Node.js-side (i'm using node-postgres in this example):

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;      

  };

});

这篇关于如果存在一行，则返回ID，否则返回INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！