ERROR: query has no destination for result data in postgres

Question

I am normalising addresses in my old_users_data table to split them into separate location and city tables. For this, I made a function that loops through old users rows and creates location and city records as required. Dbfiddle here

CREATE OR REPLACE FUNCTION test_func() 
RETURNS BOOL AS

$$
DECLARE
    temprow record;
BEGIN
FOR temprow IN 
    SELECT * FROM old_users_data oud
    LOOP

    with city as (
        insert into city (name)
        select temprow.city
        where not exists(
            select id from city where name = temprow.city
        )
        returning id),

        locations AS (
        INSERT INTO locations
            (address, city)
        select temprow.address, id from city
        RETURNING id
        )
    INSERT INTO users (name, location)
        SELECT temprow.name, id
        FROM locations
        RETURNING id;


        raise notice 'Value: %, %', temprow.id, temprow.city;
END LOOP;
RETURN TRUE;
END;
$$

LANGUAGE plpgsql;
SELECT test_func();

But I am getting the error:

ERROR:  query has no destination for result data
CONTEXT:  PL/pgSQL function test_func() line 9 at SQL statement

despite having a return statement.

Here is my data: old_users_data table

+----+--------+---------------------------+----------+
| id |  name  |          address          |   city   |
+----+--------+---------------------------+----------+
|  1 | sam    | 228 Park Ave S            | New York |
|  2 | rachel | 8474 Augusta St. Brooklyn | New York |
+----+--------+---------------------------+----------+

users table

+----+--------+-------------+
| id |  name  | location_id |
+----+--------+-------------+
|  1 | sam    |           1 |
|  2 | rachel |           2 |
+----+--------+-------------+

location table

+----+---------------------------+---------+
| id |          address          | city_id |
+----+---------------------------+---------+
|  1 | 228 Park Ave S            |       1 |
|  2 | 8474 Augusta St. Brooklyn |       1 |
+----+---------------------------+---------+

city table

+----+----------+
| id |   name   |
+----+----------+
|  1 | New York |
+----+----------+

Remove the RETURNING id; from the last INSERT in your CTE. — user330315
– user330315, Commented Apr 17, 2020 at 7:26
I already have return true at the end. I removed the RETURNING id; and the error resolved. However, only one user got inserted, not the other. dbfiddle.uk/… — jerrymouse
– jerrymouse, Commented Apr 17, 2020 at 7:30
Only one row for each unique city is getting inserted, in the new users table. dbfiddle.uk/… — jerrymouse
– jerrymouse, Commented Apr 17, 2020 at 7:32
@a_horse_with_no_name This is duplicating cities in the city table. I assume this is happening because where not exists(select * from city where city.name = oud.city) is run only in the beginning, when city table is null. So New York is inserted twice. — jerrymouse
– jerrymouse, Commented Apr 17, 2020 at 8:08

Slava Rozhnev · Accepted Answer · 2020-04-17 08:14:04Z

1

I can advice another approach to solve this problem in 3 query:

Generate cities table:

insert into city ("name") select distinct city from old_users_data;

Generate locations table:

insert into locations ("address", "city_id")
select distinct address, city.id as city_id 
from old_user_data join city on city.name = old_user_data.city;

Generate users table:

insert into users ("name", "location_id") 
select oud.name, l.id as location_id 
from old_user_data as oud
join city c on oud.city = c.name
join locaton l on l.name = oud.address and l.city_id = c.id;

edited Apr 17, 2020 at 8:14

answered Apr 17, 2020 at 8:08

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ERROR: query has no destination for result data in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related