Postgres function that loops over array using each value to query database and add record to result

I am wanting to write a Postgres (9.3) function that will take a comma seperated list of ids, creates an array from this list then loops over the array and use each id to query the database and add a record to the result set togeter with the array index. The result set also contains 2 other records based on different input parameters.

So far I have a working function that returns the first 2 records. "drwg" is a PostGIS table with id as the primary key:

CREATE OR REPLACE FUNCTION public.ft_val (
  sp integer = (-1),
  rad integer = 0,
  cp text = ''::text
)
RETURNS TABLE (
  geom public.geometry,
  no text,
  ptype char
) AS
$body$
BEGIN
	RETURN QUERY
	SELECT geometry AS geom, '' AS no, 'S' AS ptype
	FROM drwg
	WHERE id = sp
	UNION
	SELECT st_buffer(st_centroid(geometry), rad, 64), '', 'R'
	FROM drwg
	WHERE id = sp
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 20;

Open in new window


I now want to add something along the lines of:

DECLARE
  cps integer[] := string_to_array(cp, ',')::INT[];

Open in new window

...
existing select with the following added
...
    UNION
    FOREACH i IN ARRAY cps
    LOOP
    	SELECT geometry AS geom, i, 'C'
		FROM drwg
		WHERE id = cps[i]
    END LOOP

Open in new window

DerekWatlingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

earth man2Commented:
Easiest way is to create a type corresponding to the structure of data that you want to return e.g.


postgres=# select * from drwg;
 id | stuff
----+-------
  1 | hello
  2 | world
  3 | hi
(3 rows)


postgres=# create  type type001 as ( geom text, no text, ptype char );

postgres=# CREATE OR REPLACE FUNCTION ft_val ( cp text ) RETURNS SETOF type001 AS $$
postgres$# DECLARE
postgres$#   i int;
postgres$#   cps INT[] := string_to_array( cp, ',')::INT[];
postgres$#   r type001%rowtype;
postgres$# BEGIN
postgres$#   FOREACH i IN ARRAY cps LOOP
postgres$#     SELECT id::text, ' ', 'C' FROM drwg into r WHERE id = i;
postgres$#     if FOUND then
postgres$#       RETURN NEXT r;
postgres$#     end if;
postgres$#   END LOOP;
postgres$#   RETURN;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=# select * from ft_val('2,3,4');
 geom | no | ptype
------+----+-------
 2    |    | C
 3    |    | C
(2 rows)

Open in new window

0
DerekWatlingAuthor Commented:
I got the original to work by using multiple RETURN QUERYs instead of UNION.

"no" should be the array index in, so I used:
FOR i IN array_lower(cps, 1)..array_upper(cps, 1)
LOOP
...

Open in new window


This worked well in Postgres. However, the end result is to be used as a GeoServer SQL View and GeoServer would not accept this function. Fortunately the array would only have had a maximum of 15 elements. So I have ended up creating 15 arguments to replace the single array argument.
0
DerekWatlingAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for earth man2's comment #a40388649
Assisted answer: 0 points for DerekWatling's comment #a40392142

for the following reason:

The question asked that the array index be part of the result.
0
earth man2Commented:
to put loop counter in return dataset is not that difficult... eg

postgres=# \dS+ type001
              Composite type "public.type001"
 Column |     Type     | Modifiers | Storage  | Description
--------+--------------+-----------+----------+-------------
 geom   | text         |           | extended |
 no     | text         |           | extended |
 ptype  | character(1) |           | extended |


postgres=# CREATE OR REPLACE FUNCTION ft_val ( cp text ) RETURNS SETOF type001 AS $$
postgres$# DECLARE
postgres$#   i int;
postgres$#   cps INT[] := string_to_array( cp, ',')::INT[];
postgres$#   r type001%rowtype;
postgres$#   j int;
postgres$# BEGIN
postgres$#   j := 1;
postgres$#   FOREACH i IN ARRAY cps LOOP
postgres$#     SELECT id::text, 'C' FROM drwg into r.geom,r.ptype WHERE id = i;
postgres$#     r.no := j;
postgres$#     if FOUND then
postgres$#       RETURN NEXT r;
postgres$#     end if;
postgres$#     j := j + 1;
postgres$#   END LOOP;
postgres$#   RETURN;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select * from ft_val('2,3,4');
 geom | no | ptype
------+----+-------
 2    | 1  | C
 3    | 2  | C
(2 rows)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.