Function inserting multiple rows in a table

Question

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 );

"insert that output into a table I've created", you haven't shown us the table, instead you reposted the type definition. Then do you want it to to both write that output to the table and return it, or just write it? — 404
– 404, Commented Nov 24, 2018 at 14:15
Sorry, that was a mistake, I've corrected it now. The table is empty, ideally it would return the type and insert it in the table TEST2_TABLE. However, if that's not possible I'd settle for just the function filling the table — Sergio
– Sergio, Commented Nov 24, 2018 at 15:03

404 · Accepted Answer · 2018-11-24 15:21:40Z

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.

Collectives™ on Stack Overflow

Function inserting multiple rows in a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related