1

As you can see in title, I want to loop through specific column which holds building IDs and then function will execute a second query that uses these looped values and show query result in PostgreSQL.

Please note that the code below:

CREATE OR REPLACE FUNCTION getBID() RETURNS SETOF building AS
$BODY$
DECLARE
    r building%ROWTYPE;
BEGIN
    FOR r IN EXECUTE 'SELECT point_id FROM building'

    LOOP

    RETURN QUERY EXECUTE 'SELECT gid, nof, year 
    FROM building 
    WHERE point_id = ' || r;

    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getBID();

My building IDs are integer values. I wonder two aspects as follows:

  • "r" variable should be declared as "integer"?
  • my second query should be used in loop, too?

Thanks in advance...

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
seroman
  • 13
  • 1
  • 3
  • Your loop doesn't make any sense. Both queries return data from the same table, so you can simply remove the first one. And the whole function can be replaced with a single `return query select gid, nof, year from building`. There is no need for a slow and cumbersome cursor –  Mar 09 '14 at 15:07

1 Answers1

3

For a trivial task like that use a simple SQL function instead:

CREATE OR REPLACE FUNCTION get_buildings()
  RETURNS SETOF building AS
'SELECT * FROM building' LANGUAGE sql;

Or just:

SELECT * FROM building;

Here is one example for an actual FOR loop filling a single column in plpgsql:
Returning results from a function in 'select statement' format

Another example with an anonymous record:
Loop on tables with PL/pgSQL in Postgres 9.0+

Try a search. There are many more.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Could you please give me some examples that represent "for loop-approach"? Actually I need this. There are actually too many examples on internet as far as I see, but these examples substantially loop through "select * from table"-like queries. I want to understand how to loop through "select column from table"-like queries. For instance, my query'll retrieve column which holds building unique IDs and iterate over another query using these IDs. I accomplish this with python or java programming language, but I want to learn PL/pgSQL functions how to deal with this kind of cases... – seroman Mar 10 '14 at 20:59
  • @seroman: I added links to examples like you requested. Note that most of the time, a set-based approach in SQL is much faster than looping. There are exceptions ... – Erwin Brandstetter Mar 10 '14 at 21:09