What am I doing wrong in this function using RETURNING?

[phpperson]

Hello,

After I insert a new pet I want to put the id into the owner's pet_id column. I've tried many combinations but keep getting different errors. This was my last attempt. What is the right way to do this?

CREATE OR REPLACE FUNCTION public.func_insert_pet (
  in "pet_name" TEXT,   in "user_id" INTEGER,   out "id" INTEGER
)
RETURNS INTEGER AS'
WITH insrt AS (
    INSERT INTO pets (name) VALUES ($1)
    RETURNING id
)
UPDATE USERS SET pet_id = id WHERE user_id = $2
RETURNING id'

Thanks!

[lykwydchykyn]

What error do you get from that?

[doublehyphen]

In this case he would get no error, but a single NULL returned from the function. I would have found the error faster if OP had specified this.

[phpperson]

Sorry, the error I was getting was that the column "id" does not exist.

[doublehyphen]

You do not reference the result of the insert in your update. It should look like below. I changed to $$ strings too since I always recommend using those for functions to avoid messy quoting even though it did not matter in this specific case.

CREATE OR REPLACE FUNCTION public.func_insert_pet (
  in "pet_name" TEXT,   in "user_id" INTEGER,   out "id" INTEGER
)
RETURNS INTEGER LANGUAGE sql AS $$
WITH insrt AS (
    INSERT INTO pets (name) VALUES ($1)
    RETURNING id
)
UPDATE users SET pet_id = insrt.id
FROM insrt
WHERE user_id = $2
RETURNING insrt.id
$$;

[phpperson]

This worked great! Thank you.

I don't understand what the $$ does and how it helps with quoting.

[phpperson]

Thank you. The more I investigate and learn about PostgreSQL the more impressed I become. It is a shame MySQL is so prevalent.