Return the id after insert or select

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

You need to store the value that you want to return somewhere:

CREATE OR REPLACE FUNCTION f_get(ikey text)
  returns integer
  AS
$func$
DECLARE 
  l_id integer;
BEGIN
  LOCK TABLE foo IN SHARE ROW EXCLUSIVE MODE;
  INSERT INTO foo (type)
  SELECT ikey
   WHERE NOT EXISTS (
     SELECT * FROM foo WHERE type=ikey
   )
   returning id into l_id; --< store the returned ID in local variable
   return l_id; --< return this variable
END
$func$ LANGUAGE plpgsql;

You could have done that with the original function and an OUT parameter as well. In that case you just need to use returning id into out_key;

StackExchange Database Administrators Q#89643, answer score: 7