How to return multiple rows in PostgreSQL using RECORD?
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 M开发者_StackOverflow社区OSTDOC 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;
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'
According to this answer, what you need is:
RETURN QUERY SELECT DISTINCT......
精彩评论