PostgreSQL – How to Return ID After Insert or Select

concurrencyinsertplpgsqlpostgresqlpostgresql-9.3

I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this?
Also how can I return the id if the email was not inserted and it already exist in the DB? Do I need to perform another SELECT?

BEGIN;
  LOCK TABLE mailing_list IN SHARE ROW EXCLUSIVE MODE;
  INSERT INTO mailing_list (email)
  SELECT 'email'
   WHERE NOT EXISTS (
     SELECT * FROM mailing_list WHERE email='email'
   );
COMMIT;

I have tried adding the returning id but it doesn't work. I got:

query has no destination for result data

Sqlfiddle

Best Answer

@a_horse already explained how to avoid the error message you saw.

Here is a simple variant of the related version we have been referring to:

Is SELECT or INSERT in a function prone to race conditions?

CREATE OR REPLACE FUNCTION f_email_insel(_email text, OUT email_id int) AS
$func$
BEGIN

LOOP
   BEGIN  -- start inner block inside loop to handle possible exception

   SELECT INTO email_id  m.email_id FROM mailing_list m WHERE m.email = _email;

   IF NOT FOUND THEN
      INSERT INTO mailing_list (email) VALUES (_email)
      RETURNING mailing_list.email_id INTO email_id;
   END IF;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- inserted in concurrent session.
      RAISE NOTICE 'It actually happened!'; -- hardly ever happens
   END;

   EXIT WHEN email_id IS NOT NULL;          -- else keep looping
END LOOP;

END
$func$ LANGUAGE plpgsql;

SQL Fiddle.

You only need the loop to deal with a possible race condition: If a concurrent transaction writes the same email value in between SELECT and INSERT, you would get a unique violation - which is handled properly here. This is assuming a UNIQUE constraint (or a UNIQUE index) on email, obviously.

The alternative with CTEs runs as one SQL statement. So here the overhead is slightly smaller (simpler queries), but the time frame for a race condition is slightly bigger. Especially if the row frequently already exists, this is a bit faster.
Read the detailed explanation over there and pick the approach that best fits your use case.

Related Solutions

Sql-server – How to insert multiple returns of the select statement

If all students returned by the first select need to be combined with all faculties returned by the second select, then you just need to cross-join the two subsets and select from the resulting set. You can use either the classic comma join or the explicit CROSS JOIN syntax:

Comma join:

INSERT INTO
  dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
  s.StudentID
, f.FacultyID
, GETDATE()
FROM
  (
    SELECT Student.StudentID 
    FROM dbo.Student 
    WHERE Student.Section = 'Section1'
  ) AS s
, (
    SELECT Faculty.FacultyID 
    FROM dbo.Faculty 
    WHERE Faculty.FirstName = @FacultyName 
    OR Faculty.LastName = @FacultyName
  ) AS f
;

CROSS JOIN:

INSERT INTO
  dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
  s.StudentID
, f.FacultyID
, GETDATE()
FROM
  (
    SELECT Student.StudentID 
    FROM dbo.Student 
    WHERE Student.Section = 'Section1'
  ) AS s
  CROSS JOIN
  (
    SELECT Faculty.FacultyID 
    FROM dbo.Faculty 
    WHERE Faculty.FirstName = @FacultyName 
    OR Faculty.LastName = @FacultyName
  ) AS f
;

Those variations are using your subselects without any change. You could also rewrite the query and cross-join the tables first before filtering both in the same WHERE:

INSERT INTO
  dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
  s.StudentID
, f.FacultyID
, GETDATE()
FROM
  dbo.Student AS s
, dbo.Faculty AS f
-- or: CROSS JOIN dbo.Faculty AS f
WHERE
  s.Section = 'Section1'
  AND (f.FirstName = @FacultyName 
    OR f.LastName = @FacultyName)
;

Note that you need to put the OR-ed Faculty filters in brackets in order to combine them with the Student filter properly. Without the brackets the result would not match the desired logic due to precedence rules for the logic operators in Transact-SQL: AND would be evaluated first, OR second, while you would want it to be the other way round, hence the brackets.

MySQL – Insert New Row After Performing Checks in Other Tables

For checking if 4 or ciad is the valid value or not you can define ciad as the foreign key in your personal_transaction table referencing the primary key of the commercial_account table. Please check the usage here: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Try this:

INSERT INTO personal_transaction (id, type, date, fund, paid, caid)
SELECT 3, credit, `2016-12-28`, 30, 2, 4
where (SELECT credit FROM personal_account WHERE id = 2) >= 30;

I have assumed fund and paid are credit value and id fields respectively in personal_account table please adjust query according to actual fields names and try:

INSERT INTO personal_transaction (id, type, date, fund, paid, caid)
SELECT 3, credit, `2016-12-28`, 30, 2, 4
where (SELECT fund FROM personal_account WHERE paid = 2) >= 30;

Best Answer

Related Solutions

Sql-server – How to insert multiple returns of the select statement

MySQL – Insert New Row After Performing Checks in Other Tables

Related Question