Solved

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

Posted on 2014-10-16
4
2,785 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to convert this query to postgres 10 754
Problem with learning MANY to MANY relation in SQL 14 197
Postgresql 7 88
postrgesql script problem 10 32
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now