Ytukyg

So I have the following tables.

             G

__________________________

id_musician  |    id_album

--------------------------

1            |    51

3            |    52

2            |    53

3            |    54

1            |    55

3            |    56



            C

__________________________

id_album    |      year

--------------------------

51          |    1990

52          |    2001

53          |    1990

54          |    2001

55          |    1945

56          |    1945

I've created the following function:

CREATE OR REPLACE FUNCTION test2 (year1 INTEGER, p_type VARCHAR(1))

RETURNS SETOF test2 AS $$

DECLARE

output test2;

BEGIN

IF p_type='S' THEN

FOR output IN SELECT g.id_artist, c.year, COUNT(c.id_album) AS albums

FROM G g, C c

WHERE g.id_album = c.id_album AND

c.year = year1

GROUP BY c.year, g.id_musician

LOOP

RETURN NEXT output;

END LOOP;

END IF

RETURN;

END;

$$LANGUAGE plpgsql;

test2 is a type of output I created:

CREATE TYPE test2 AS(

        id smallint,

        year smallint,

        total_albums integer)

The function accepts a year and type of perfomer. It returns, for every year and performer (in this case Guitarist, 'G'), the amount of records a performer has participated in every year.

What I would like is for the function to insert that output into a table I've created, instead of just showing the output:

CREATE TABLE TEST2_TABLE (

id smallint,

year smallint,

total_albums integer );

Here's the function:

CREATE OR REPLACE FUNCTION test2 (year1 INTEGER, p_type VARCHAR(1))

    RETURNS SETOF test2 AS

$BODY$

BEGIN

    IF p_type='S' THEN

        RETURN QUERY (

            WITH inserted AS (

                INSERT INTO test2_table

                SELECT g.id_musician, c.year, COUNT(c.id_album)::INTEGER AS albums

                FROM g, c

                WHERE g.id_album = c.id_album

                AND c.year = year1

                GROUP BY c.year, g.id_musician

                RETURNING *

            )

            SELECT *

            FROM inserted

        );

    ELSE

        RETURN;

    END IF;

END

$BODY$

    LANGUAGE plpgsql;

So the signature and output is the same as your original function. Main differences are:

• No need to use a loop and read/return one row at a time. You can return the result of your query directly. Much faster like that.


• To accomplish both inserting into a table and returning the same results, it uses a CTE which inserts the data in the table and returns everything that was inserted, then selects from that CTE as the return output.

I've also removed the aliases. Any identifier in postgres is lowercase, unless in double quotes. Since G and C were not in quotes, the table names are actually g and c, respectively. So I just used the actual lowercase table names.