Tag Info

New answers tagged

2

@a_horse already explained how to avoid the error message you saw. Here is a simple variant of the related version we have been referring to: Is SELECT or INSERT in a function prone to race conditions? CREATE OR REPLACE FUNCTION f_email_insel(_email text, OUT email_id int) AS $func$ BEGIN LOOP BEGIN -- start inner block inside loop to handle ...


3

You need to store the value that you want to return somewhere: CREATE OR REPLACE FUNCTION f_get(ikey text) returns integer AS $func$ DECLARE l_id integer; BEGIN LOCK TABLE foo IN SHARE ROW EXCLUSIVE MODE; INSERT INTO foo (type) SELECT ikey WHERE NOT EXISTS ( SELECT * FROM foo WHERE type=ikey ) returning id into l_id; --< store ...


1

The hint of the error message is right on target: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause. Bold emphasis mine. Per documentation: RETURNING queries on the view will be rejected if there is no RETURNING clause in any available rule. Don't confuse this with the RETURNS clause of your function. Not the same ...



Top 50 recent answers are included