INSERT if not exists, else return id

Question

There are some limitations:

1. Cannot modify database
2. Columns are not unique
3. Needs to return last insert id (RETURNING id)
4. If exists, return existing id
5. It will be called through our custom db library (the values in select will be as parameters from PHP (?, ?, ?, !))

INSERT INTO tags (name, color, groupid)
SELECT  'test', '000000', 56
WHERE NOT EXISTS (
  SELECT text FROM tags WHERE name = 'test' AND groupid = 56
)
RETURNING id

This works - until to the point where i need to get existing id aswell. With this i only get the inserted id. Is it possible to return the value of SELECT statement if it doesn't insert?

UPDATE:

DO $$
BEGIN
    IF NOT EXISTS (
      SELECT text FROM tags WHERE name = 'test' AND groupid = 56
    )
    THEN
      INSERT INTO tags (name, color, groupid)
          VALUES  ('test', '000000', 56) RETURNING id;
    ELSE
      RETURN SELECT text FROM tags WHERE name = 'test' AND groupid = 56;
    END IF;
END
$$

Was testing with this kind of format - however this ends with a few errors:

RETURN cannot have a parameter in function returning void

Answer 1

You can do this using a CTE.

The info cte has the source data, so replace the values there with your placeholders for PHP.

The CTE will return the results from the first half of the union if an existing record exists (so will show old id), and the second half if an insert was performed (so will show the new id).

WITH info (name, color, groupid) AS
  (values('test','000000',56)),
  trial AS (
    INSERT INTO tags(name, color, groupid)
      SELECT info.name, info.color, info.groupid
      FROM info
      WHERE NOT EXISTS (
        SELECT * FROM tags t2
        WHERE t2.name = info.name AND t2.groupid= info.groupid)
    RETURNING id)
SELECT tags.id
FROM tags
INNER JOIN info ON tags.name = info.name AND tags.groupid= info.groupid
UNION ALL
SELECT trial.id
FROM trial;

SQL Fiddle example: http://sqlfiddle.com/#!15/a7b0f/2

Postgres manual page for using CTEs

http://www.postgresql.org/docs/9.1/static/queries-with.html