How to return multiple rows in PostgreSQL using RECORD?

Question

I'm trying to create a function which runs an SQL query on multiple tables and outputs the resulting table from the query. The resulting table will have multiple rows. I'm having a lot of difficulty with this and I've read posts which suggest using RETURN NEXT but I haven't been able to get that to work either. From what I understand RECORD can be used because it takes the values of the data fed into it. Here is my code so far:

CREATE OR REPLACE FUNCTION
    most_docs()
RETURNS
    SETOF RECORD
AS $$
DECLARE
    result RECORD;
BEGIN
    CREATE VIEW MOSTDOC AS
    SELECT P.country, COUNT(P.country) AS cnt
    FROM Producer P, Movie M, ProducerMovie PM
    WHERE M.title = PM.title
    AND M.year = PM.year
    AND P.name = PM.name
    AND M.genre = 'Documentary'
    GROUP BY P.country;

    SELECT DISTINCT M.country INTO result
    FROM MOSTDOC M
    WHERE M.cnt = (SELECT MAX(M.cnt)
    FROM MOSTDOC M);

    RETURN result;
END;
$$ LANGUAGE plpgsql;

Any help would be much appreciated. Thanks.

---------- Word in Progress code

CREATE OR REPLACE FUNCTION
    most_docs()
RETURNS
    SETOF RECORD
AS $$
DECLARE
result RECORD
BEGIN
    CREATE VIEW MOSTDOC AS
    SELECT P.country, COUNT(P.country) AS cnt
    FROM Producer P, Movie M, ProducerMovie PM
    WHERE M.title = PM.title
    AND M.year = PM.year
    AND P.name = PM.name
    AND M.genre = 'Documentary'
    GROUP BY P.country;

    RETURN QUERY SELECT DISTINCT M.country
    FROM MOSTDOC M
    WHERE M.cnt = (SELECT MAX(M.cnt)
    FROM MOSTDOC M);
END;
$$ LANGUAGE plpgsql;

ertx · Accepted Answer · 2011-08-12 08:24:22Z

3

If i understand your problem correctly, your'e trying to do something like this (for functions that return setof i always use types)

CREATE TYPE frt_test_type AS
   (
    country character varying,
    cnt integer,
    country character varying); /* types may vary */

CREATE OR REPLACE FUNCTION
  RETURNS SETOF frt_test_type AS
$BODY$DECLARE r record;
BEGIN
  for r in     SELECT P.country, COUNT(P.country) AS cnt, 
FROM Producer P, Movie M, ProducerMovie PM
WHERE M.title = PM.title
AND M.year = PM.year
AND P.name = PM.name
AND M.genre = 'Documentary'
GROUP BY P.country;
  loop
      return next r; 
  end loop;
  return; 
END;$BODY$
  LANGUAGE 'plpgsql'

answered Aug 12, 2011 at 8:24

ertx

1,5442 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:34:22Z

0

According to this answer, what you need is:

RETURN QUERY SELECT DISTINCT......

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Aug 12, 2011 at 0:06

thelsdj

9,20013 gold badges47 silver badges59 bronze badges

5 Comments

tvguide1234 Over a year ago

Does this mean that I don't need to declare a variable result RECORD and delete the corresponding RETURN result line? If I do this I get "a column definition list is required for fuctions returning 'record'"

thelsdj Over a year ago

I think you still need result RECORD but can leave out the RETURN result

tvguide1234 Over a year ago

Hmm I'm getting the same error. I updated the question to show the new code. If it makes a difference the way I'm calling the function is SELECT * most_docs(). Is this correct syntax?

thelsdj Over a year ago

I believe SELECT * FROM most_docs() is correct. You may have to cast M.country to the proper data type in the RETURN QUERY line like: M.country::text or whatever the type is.

tvguide1234 Over a year ago

How can I put that in correct syntax. RETURN QUERY SELECT DISTINCT M.country M.country%TYPE ?

Collectives™ on Stack Overflow

How to return multiple rows in PostgreSQL using RECORD?

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related