Supabase : PGSQL Function will not Validate-CodePudding

I am trying to create a PGSQL function that uses HTTP to get some json, format it, and insert it into a table using the donation_id key as a row constraint to prevent duplicates.

I have tried this function:

BEGIN
INSERT INTO donations(
    donation_id, amount, avatar_image_url, created_date_utc, display_name, donor_id, event_id, incentive_id, message, participant_id, team_id) 
    ON CONFLICT (donation_id) DO NOTHING
SELECT elms::jsonb->>'donationID' AS donation_id ,
    (elms::jsonb->>'amount')::float8 AS amount ,
    elms::jsonb->>'avatarImageURL' AS avatar_image_url ,
    (elms::jsonb->>'createdDateUTC')::timestamptz AS created_date_utc ,
    elms::jsonb->>'displayName' AS display_name ,
    elms::jsonb->>'donorID' AS donor_id ,
    (elms::jsonb->>'eventID')::int AS event_id ,
    elms::jsonb->>'incentiveID' AS incentive_id ,
    elms::jsonb->>'message' AS message ,
    (elms::jsonb->>'participantID')::int AS participant_id ,
    (elms::jsonb->>'teamID')::int AS team_id
    FROM (
        select jsonb_array_elements(content::jsonb) AS elms
        from http_get('https://extralife.donordrive.com/api/teams/59881/donations/')) as alias;
END;

I'm not quite understanding what I am doing wrong with the ON CONFLICT part of the query, just that it is apparently not valid syntax. I appreciate the insight as I'm not quite grasping the explainer written in docs.

CodePudding user response：

Assuming a test table:

drop table if exists donations;
create table donations (
 donation_id        text primary key,
 amount             float8,
 avatar_image_url   text,
 created_date_utc   timestamptz,
 display_name       text,
 donor_id           text,
 event_id           int,
 incentive_id       text,
 message            text,
 participant_id     int,
 team_id            int);

It will work once you move the ON CONFLICT (donation_id) DO NOTHING to the end of the query:

INSERT INTO donations(donation_id, amount, avatar_image_url, created_date_utc,
        display_name, donor_id, event_id, incentive_id, message, participant_id, 
        team_id) 
SELECT  elms::jsonb->>'donationID'          AS donation_id ,
       (elms::jsonb->>'amount')::float8     AS amount ,
        elms::jsonb->>'avatarImageURL'      AS avatar_image_url ,
       (elms::jsonb->>'createdDateUTC')::timestamptz AS created_date_utc ,
        elms::jsonb->>'displayName'         AS display_name ,
        elms::jsonb->>'donorID'             AS donor_id ,
       (elms::jsonb->>'eventID')::int       AS event_id ,
        elms::jsonb->>'incentiveID'         AS incentive_id ,
        elms::jsonb->>'message'             AS message ,
       (elms::jsonb->>'participantID')::int AS participant_id ,
       (elms::jsonb->>'teamID')::int        AS team_id
FROM (  select jsonb_array_elements('[
                  {
                    "displayName": "Christine",
                    "donorID": "A05C2C1E5DE15CDC",
                    "links": {
                      "recipient": "https://assets.yourdomain.com/somelink"
                    },
                    "eventID": 552,
                    "createdDateUTC": "2022-09-18T14:08:35.227 0000",
                    "recipientName": "Have A Drink Show",
                    "participantID": 494574,
                    "amount": 50,
                    "avatarImageURL": "https://assets.yourdomain.com/asset.gif",
                    "teamID": 59881,
                    "donationID": "FDBB61C5C8FFB3AE"
                  }
                ]'::jsonb) AS elms) as alias
ON CONFLICT (donation_id) DO NOTHING;

Demo.