Using a row as a table in a query within a function PLpgSQL

Question

I am trying to write a plpgsql function that loops through a table. On each loop, it pulls a row from the table, stores it in a record, then uses that record in the join clause of a query. Here is my code:

CREATE OR REPLACE FUNCTION "testfncjh2" ()  RETURNS int
  IMMUTABLE
  SECURITY DEFINER
AS $dbvis$
DECLARE 
        counter int;
        tablesize int;
        rec1 record;
        tablename text;
        rec2 record;
BEGIN
counter = 0;
for rec1 in  SELECT * FROM poilocations_sridconv loop  

raise notice 'here';   
execute $$ select count(*) from $$||rec1||$$ $$ into tablesize;

        while counter < tablesize loop

                counter = counter + 1;
                raise notice 'hi';
                execute $$ select count(*) from cities_sridconv $$ into tablesize;
                end loop; 
    end loop;
return counter;
END;
$dbvis$ LANGUAGE plpgsql;

Each time I run this, I get the following error:

ERROR: could not find array type for data type record

Is there a way to use the row as a table in the query within the nested loops?

My end goal is to build a function that loops through a table, pulling a row from that table on each loop. In each loop, a number COUNTER is computed using the row, then a query is executed depending on the row and COUNTER. Knowing that this code is currently very flawed, I am posting it below to give an idea of what I am trying to do:

CREATE OR REPLACE FUNCTION "testfncjh" () RETURNS void IMMUTABLE SECURITY DEFINER AS $dbvis$ DECLARE counter int; tablesize int; rec1 record; tablename text; rec2 record; BEGIN

for rec1 in  SELECT * FROM poilocations_sridconv loop  
        counter = 0;
        execute $$ select count(*)
                from    $$||rec1||$$ a
                join
                        cities_srid_conv b
                on      right(a.geom_wgs_pois,$$||counter||$$) = right(b.geom_wgs_pois,$$||counter||$$) $$ into tablesize;

        raise notice 'got through first execute';
        while tablesize = 0 loop

                counter = counter + 1;
                execute $$ select count(*)
                from    '||rec1||' a
                join
                        cities_srid_conv b
                on      right(a.geom_wgs_pois,'||counter||') = right(b.geom_wgs_pois,'||counter||') $$ into tablesize;
                raise notice 'hi';
                end loop;

        EXECUTE
                'select 
                         poiname,
                         name as cityname, 
                         postgis.ST_Distance(postgis.ST_GeomFromText(''POINT(poilat poilong)''),
                                             postgis.ST_GeomFromText(''POINT(citylat citylong)'')
                                             ) as distance
                from    (select a.poiname, 
                                a.latitude::text as poilat, 
                                a.longitude::text as poilong,
                                b.geonameid, 
                                b.name, 
                                b.latitude as citylat, 
                                b.longitude as citylong
                        from '||rec1||' a 
                        join cities_srid_conv b
                        on  right(a.geom_wgs_pois,'||counter||') = right(b.geom_wgs_pois,'||counter||'))
                        ) x
                 order by distance
         limit 1'

        poi_cities_match (poiname, cityname, distance);  ------SQL STATEMENT TO INSERT CLOSEST CITY TO TABLE POI_CITIES_MATCH 
    end loop;

END;
$dbvis$ LANGUAGE plpgsql;

I am running on a PostgreSQL 8.2.15 database.

Also, sorry for reposting. I had to remove some data from the original.

score 0 · Accepted Answer · answered Mar 02 '16 at 23:13

I think you should be able to use composite types for what you want. I simplified your top example and used composite types in the following way.

CREATE OR REPLACE FUNCTION "testfncjh2" ()  RETURNS int
  IMMUTABLE
  SECURITY DEFINER
AS $dbvis$
DECLARE 
        counter int;
        tablesize int;
        rec1 poilocations_sridconv;
        tablename text;
        rec2 record;
BEGIN
counter = 0;
for rec1 in  SELECT * FROM poilocations_sridconv loop  
  raise notice 'here';
  select count(*) FROM (select (rec1).*)theRecord into counter; 
end loop;
return counter;
END;
$dbvis$ LANGUAGE plpgsql;

The main changes being the rec1 poilocations_sridconv; line and using (select (rec1).*)

Hope it helps.

EDIT: I should note that the function is not doing the same thing as it does in the question above. This is just as an example of how you could use a record as a table in a query.

score 0 · Answer 2 · answered Mar 02 '16 at 23:51

You have a few issues with your code (apart, perhaps, from your logic).

Foremost, you should not use a record as a table source in a JOIN. Instead, filter the second table for rows that match some field from the record.

Second, you should use the format() function instead of assembling strings with the || operator. But you can't because you are using the before-prehistoric version 8.2. This is from the cave-painting era (yes, it's that bad). UPGRADE!

Thirdly, don't over-complicate your queries. The sub-query is not necessary here.

Put together, the second dynamic query from your real code would reduce to this:

EXECUTE format(
  'SELECT b.name, 
          postgis.ST_Distance(postgis.ST_SetSRID(postgis.ST_MakePoint(%1$I.longitude, %1$I.latitude), 4326),
                              postgis.ST_SetSRID(postgis.ST_MakePoint(b.longitude, b.latitude), 4326))
   FROM cities_srid_conv b
   WHERE right(%1$I.geom_wgs_pois, %2$L) = right(b.geom_wgs_pois, %2$L)
   ORDER BY distance
   LIMIT 1', rec1, counter) INTO cityname, distance;

poi_cities_match (rec1.poiname, cityname, distance);  ------SQL STATEMENT TO INSERT CLOSEST CITY TO TABLE POI_CITIES_MATCH

Here %1$I refers to the first parameter after the string, which is an idenifier: rec1; %2$L is the second parameter, being a literal value: counter. I leave it to yourself to re-work this to a pre-8.4 string concatenation. The results from the query are stored in a few additional variables which you can then use in the following function call.

Lastly, you had longitude and latitude reversed. In PostGIS longitude always comes first.

Using a row as a table in a query within a function PLpgSQL

2 Answers2