CODEWITHSUNDEEP
postgresqlpostgresql-9.1postgresql-9.2postgresql-9.5
Sergio
Sergio

Reputation: 53

Function inserting multiple rows in a table

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

Upvotes: 0

Views: 159

Answers (1)

404
404

Reputation: 8542

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.

Upvotes: 1

Related Questions