CODEWITHSUNDEEP
postgresqlfor-loopplpgsql
seroman
seroman

Reputation: 13

PL/pgSQL functions - loop through specific column and execute second query in loop

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:

Thanks in advance...

Upvotes: 1

Views: 2720

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658717

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.

Upvotes: 3

Related Questions