Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4333
  • Last Modified:

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

0
DerekWatling
Asked:
DerekWatling
  • 2
  • 2
2 Solutions
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now