PostgreSQL - Function to return multiple columns

Question

Here's a function that provides a result of 2 columns.

In this function there's a Loop been used to return the result.

Function :

Create Type Repeat_rs as (
label text,
count bigint
)

CREATE OR REPLACE FUNCTION Repeat(fromDate date,toDate date)
returns SETOF  Repeat_rs
AS 
$$
Declare someVariableName Repeat_rs;
BEGIN
    For someVariableName in (
        SELECT label, count(*) AS Cnt from test where date between fromDate and toDate group by circle
    ) Loop
    Return Next someVariableName;
    End Loop;
    Return;
END;
$$
LANGUAGE plpgsql;

Is there any possibilities of returning the rows without using loop?

If so please do share me on how we can do it.

And will I be able to write a function to insert records on to a table without using loop?

Help me out to solve this query.

Thanks in advance.

I don't which one to accept but both gave me the answer what i was expecting for. Hence +1 for both the answers. — Unknown User
– Unknown User, Commented Apr 16, 2014 at 10:56
Note that your query is not valid: you group only by circle, but use label as a selected column. postgresql.org/docs/9.3/static/sql-select.html#SQL-GROUPBY — pozs
– pozs, Commented Apr 16, 2014 at 12:15
That needs to label. That was a typo error. By the way the query will not run if i don't include label in the groupby. — Unknown User
– Unknown User, Commented Apr 16, 2014 at 12:47
You should accept a_horse_with_no_name's answer if it answers your question. — Kenny Evitt
– Kenny Evitt, Commented May 17, 2014 at 0:27

score 20 · Accepted Answer · 2014-04-16 12:22:04Z

20

you don't need the extra type definition. And to return multiple rows, use return query:

Something like this:

CREATE OR REPLACE FUNCTION Repeat(fromDate date,toDate date)
  returns table (label text, cnt bigint)
AS 
$$
BEGIN
    Return query 
       SELECT label, count(*) AS Cnt 
       from test 
       where date between fromDate and toDate
       group by label;
END;
$$
LANGUAGE plpgsql;

You don't even need a PL/pgSQL function, you can use a simple SQL function for this:

CREATE OR REPLACE FUNCTION Repeat(fromDate date, toDate date)
  returns table (label text, cnt bigint)
AS 
$$
   SELECT label, count(*) AS Cnt 
   from test 
   where date between fromDate and toDate
   group by label;
$$
LANGUAGE sql;

edited Apr 16, 2014 at 12:22

answered Apr 16, 2014 at 10:20

user330315

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

1 Comment

pozs Over a year ago

+1, but forgot the group by circle clause. Also label is not a valid column if it's not in the group by clause.

Ilesh Patel · Accepted Answer · 2014-04-16 10:20:49Z

-2

You can insert values in the table using SELECT query as below:

CREATE OR REPLACE FUNCTION Repeat(fromDate date,toDate date)
returns VOID 
AS 
$$

BEGIN
    INSERT INTO TABLE_NAME
         SELECT label, count(*) AS Cnt from test where date between fromDate and toDate group by circle
END;
$$
LANGUAGE plpgsql;

answered Apr 16, 2014 at 10:20

Ilesh Patel

2,15516 silver badges27 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Function to return multiple columns

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related