Solved

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

Posted on 2014-10-16
4
3,171 Views
Last Modified: 2014-10-23
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
Comment
Question by:DerekWatling
  • 2
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 500 total points
ID: 40388649
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
 

Author Comment

by:DerekWatling
ID: 40392142
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
 

Author Comment

by:DerekWatling
ID: 40399444
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 40393849
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question