How to return `SETOF RECORD` type from a function in PostgreSQL?

Question

I'm trying to create a function which will return setof record. I want to use the function as follows:

SELECT city_name FROM set_city(1, 1, 'ExampleName');

My function:

CREATE OR REPLACE FUNCTION set_city(_city_id integer, _country_id integer, _city_name varchar)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
  result RECORD;
BEGIN
  IF EXISTS (SELECT 1 FROM geo_cities gc WHERE gc.id = _city_id) THEN
    UPDATE geo_cities
    SET country_id = _country_id, city_name = _city_name
    WHERE id = _city_id
    RETURNING * INTO result;
  ELSE
    INSERT INTO geo_cities(id, country_id, city_name)
    VALUES (_city_id, _country_id, _city_name)
    RETURNING * INTO result;
  END IF;
  -- It's wrong
  RETURN QUERY SELECT result;
END;
$$

What should I change?

Answer 1

You could change the return statement:

...
        -- It's wrong
        -- RETURN QUERY SELECT result;
        RETURN NEXT result; -- that's good
...

However, a column definition list is required for functions returning "record", so you would have to add it in every query:

SELECT city_name FROM set_city(1, 1, 'ExampleName') 
    AS (id int, country_id int, city_name text);

In fact the function returns a single row of the type geo_cities and you do not need setof:

DROP FUNCTION set_city(_city_id integer, _country_id integer, _city_name varchar);

CREATE OR REPLACE FUNCTION set_city(_city_id integer, _country_id integer, _city_name varchar)
RETURNS geo_cities
LANGUAGE plpgsql
as $$
DECLARE
        result geo_cities;
BEGIN
        IF EXISTS (SELECT 1 FROM geo_cities gc WHERE gc.id = _city_id)
        THEN
                UPDATE geo_cities
                SET country_id = _country_id, city_name = _city_name
                WHERE id = _city_id
                RETURNING * INTO result;
        ELSE
                INSERT INTO geo_cities(id, country_id, city_name)
                VALUES (_city_id, _country_id, _city_name)
                RETURNING * INTO result;
        END IF;
        RETURN result;
END;
$$;

SELECT city_name FROM set_city(1, 1, 'ExampleName');

Note that you can get the same functionality in a single SQL statement:

INSERT INTO geo_cities(id, country_id, city_name)
VALUES (1, 1, 'ExampleName')
ON CONFLICT (id) DO UPDATE SET 
    country_id = excluded.country_id, 
    city_name = excluded.city_name
RETURNING *;