php - INSERT if not exists, else return id -
there limitations:
- cannot modify database
- columns not unique
- needs return last insert id (
returning id
) - if exists, return existing id
- it called through our custom db library (the values in select parameters php (?, ?, ?, !))
insert tags (name, color, groupid) select 'test', '000000', 56 not exists ( select text tags name = 'test' , groupid = 56 ) returning id
this works - until point need existing id aswell. inserted id. possible return value of select statement if doesn't insert?
update:
do $$ begin if not exists ( select text tags name = 'test' , groupid = 56 ) insert tags (name, color, groupid) values ('test', '000000', 56) returning id; else return select text tags name = 'test' , groupid = 56; end if; end $$
was testing kind of format - ends few errors:
return cannot have parameter in function returning void
you can using cte.
the info cte has source data, replace values there placeholders php.
the cte return results first half of union if existing record exists (so show old id), , second half if insert performed (so show new id).
with info (name, color, groupid) (values('test','000000',56)), trial ( insert tags(name, color, groupid) select info.name, info.color, info.groupid info not exists ( select * tags t2 t2.name = info.name , t2.groupid= info.groupid) returning id) select tags.id tags inner join info on tags.name = info.name , tags.groupid= info.groupid union select trial.id trial;
sql fiddle example: http://sqlfiddle.com/#!15/a7b0f/2
postgres manual page using ctes
Comments
Post a Comment