postgreqlupsertlua

Changed 2013-04-28 17:12:08


    postgresql.upsert.lua

    -- use postgresql "upsert" using writable CTE (psql 9.1 feature)
    local sql = [[
    WITH new_values (rss_feed, guid, title, url, pubDate, content) AS (
      VALUES
         ]]..sprintf('(%s, %s, %s, %s, %s::timestamp, %s)', rss_feed, quote(guid), quote(e.title), quote(e.link), quote(e.updated), quote(content))..[[
    ),
    upsert as
    (
        UPDATE rss_item m
            SET rss_feed = nv.rss_feed,
                guid = nv.guid,
                title = nv.title,
                url = nv.url,
                pubDate = nv.pubDate,
                content = nv.content
        FROM new_values nv
        WHERE m.rss_feed = nv.rss_feed
        AND m.guid = nv.guid
        RETURNING m.*
    )
    INSERT INTO rss_item (rss_feed, guid, title, url, pubDate, content)
    SELECT rss_feed, guid, title, url, pubDate, content
    FROM new_values
    WHERE NOT EXISTS (SELECT 1
                      FROM upsert up
                      WHERE up.rss_feed = new_values.rss_feed
                      AND up.guid = new_values.guid)
    ]]
            local res = db.dbreq(sql)