Returning from a rule with extended query protocol

From: Greg Rychlewski <greg(dot)rychlewski(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Returning from a rule with extended query protocol
Date: 2024-08-11 11:29:25
Message-ID: CAKemG7U4r2yFCoQEkLWH0N9B9tMtCX_OmGkqpmyfZtt55-NFRg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I was testing creating a rule that uses RETURNING and noticed a difference
between the extended query protocol and the simple query protocol. In the
former, RETURNING is ignored (at least in my case) and the latter it is
respected:

CREATE table test (id bigint, deleted boolean);

CREATE RULE soft_delete AS ON DELETE TO test DO INSTEAD (UPDATE test set
deleted = true WHERE id = old.id RETURNING old.*);

INSERT INTO test values (1, false);

# extended protocol result

postgres=# DELETE FROM test WHERE id = $1 RETURNING * \bind 1 \g

DELETE 0

# simple protocol result

postgres=# DELETE FROM test WHERE id = 1 RETURNING *;

id | deleted

----+---------

1 | t

(1 row)

DELETE 0

I was wondering if this is something that is just fundamentally not
expected to work or if it might be able to work without jeopardizing
critical parts of Postgres. If the latter I was interested in digging
through the code and seeing if I could figure it out.

Note that I work on a driver/client for Postgres and the example above came
from a user. I'm not sure if it's the best way to do what they want but
their question sparked my interest in the general behaviour of returning
from rules with the extended query protocol.

Thanks

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2024-08-11 12:08:26 Re: Restricting Direct Access to a C Function in PostgreSQL
Previous Message Pavel Stehule 2024-08-11 09:41:11 Re: Restricting Direct Access to a C Function in PostgreSQL