CODEWITHSUNDEEP
sqldatabasepostgresqlplpgsqlreturn-query
Vitaly
Vitaly

Reputation: 145

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

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?

Upvotes: 1

Views: 6185

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you can create my_func() which returns SETOF RECORD type with a RETURN NEXT or RETURN QUERY statement as shown below:

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
DECLARE 
  row RECORD;
BEGIN
  FOR row IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT row; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
BEGIN
  RETURN QUERY VALUES ('John','Smith'), ('David','Miller'); -- Here
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns 2 rows as shown below:

postgres=# SELECT * FROM my_func() AS (first_name TEXT, last_name TEXT);
 first_name | last_name
------------+-----------
 John       | Smith
 David      | Miller
(2 rows)

In addition, you can create my_func() which returns TABLE() with a RETURN NEXT or RETURN QUERY statement as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(first_name TEXT, last_name TEXT) AS $$
BEGIN
  FOR first_name, last_name IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func() RETURNS TABLE(first_name TEXT, last_name TEXT) AS $$
BEGIN
  RETURN QUERY VALUES ('John','Smith'), ('David','Miller'); -- Here
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns 2 rows as shown below:

postgres=# SELECT * FROM my_func();
 first_name | last_name
------------+-----------
 John       | Smith
 David      | Miller
(2 rows)

postgres=# SELECT my_func();
    my_func
----------------
 (John,Smith)
 (David,Miller)
(2 rows)

Upvotes: 2

klin
klin

Reputation: 121604

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 *;

Upvotes: 5

Related Questions