How to Create PostgreSQL UPSERT Function that Accessing CTE Table Inner IF Statement?

Question

I want to create upsert function that accessing CTE Table inner IF Clause, but i found Error :

ERROR: syntax error at or near "ON"

LINE 12: SELECT id , 999 FROM list ON CONFLICT (id) ^

********** Error **********

ERROR: syntax error at or near "ON" SQL state: 42601 Character: 346

Data in hasil_perhitungan table:

----------
id + hasil
---+------
21 + 666
22 + 777
23 + 777
24 + 777
25 + 777
---+------

Data in list_di :

---
id 
---
19
20
21
---

I have to try this :

postgres syntax error at or near "ON"

and

UPSERT Postgres 9.5

CREATE OR REPLACE FUNCTION coba(input_data integer)
  RETURNS void AS
  $BODY$
BEGIN
IF EXISTS (SELECT id, CASE WHEN id = input_data THEN 'OK' END FROM hasil_perhitungan)
    THEN 
    WITH result AS (
    SELECT id FROM hasil_perhitungan)
    , list AS (
    SELECT id FROM list_di)
    INSERT INTO hasil_perhitungan 
    SELECT id , 999 FROM list ON CONFLICT (id)
        UPDATE SET hasil = 888;
END IF;
END;
$BODY$
LANGUAGE plpgsql;

I have expected when i execute the function with IF statement and integer parameter that exist that can accessing CTE table inner the IF Statement. so When I run function with :

SELECT coba(25);

The Function will insert and update so i get :

Data in hasil_perhitungan table :

----------
id + hasil
---+------
19 + 999
20 + 999
21 + 888
22 + 777
23 + 777
24 + 777
25 + 777
---+------

score 0 · Answer 1 · answered Feb 19 '19 at 03:25

You have several issues with your code

There's no need for with clauses to run an INSERT ON CONFLICT
DO keyword is missing in your query
You probably don't require that IF EXISTS condition because that would mean you will always want an update and not insert. Even if you need it, that's not the right way of writing an EXISTS check, It should be something like:

IF EXISTS ( SELECT 1 FROM hasil_perhitungan WHERE id = input_data ) THEN

Perhaps this simple function should be sufficient to fulfil your requirement.

CREATE OR REPLACE FUNCTION coba(input_data integer)
  RETURNS void AS
  $BODY$
BEGIN
    INSERT INTO hasil_perhitungan 
    SELECT id , 999 FROM list_di ON CONFLICT (id)
       DO UPDATE SET hasil = 888;
END;
$BODY$
LANGUAGE plpgsql;

Here's a demo of the working code for the above

Oke Thanks before Mr. Nayak, But for my real case i need: 1. CTE (With clauses), cause i need to join some table and made it as CTE, and select the CTE then insert it to a table 2. I need IF Else statement cause befor it i need to filtering some data that i have to use IF Else the function above it just like algorithm of my function in the simple way, i need to make upsert function that need access CTE inner IF ELSE clause statement.. Thanks a lot Mr. Nayak, The Best Regards — M. Fitriawan, Feb 21 '19 at 08:09

How to Create PostgreSQL UPSERT Function that Accessing CTE Table Inner IF Statement?

1 Answers1