PostgreSQL Function Insert Issue – Fixing Single Row Insert Problem

functionsplpgsqlpostgresqlpostgresql-9.3

Postgresql 9.3, Mac OS X 10.6.7

I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows.

First, here is the Table T1:

 test_app_development=# select * from T1;
   name  | age | id
  -------+-----+----
   jim   |  50 |  1
  bill   |  55 |  2
   Ed    |  35 |  3
   joe   |  30 |  4
  dave   |  45 |  5
  steve  |  38 |  6
   (6 rows)

Now the Function:

  create or replace function stats_test() returns numeric as $$
    begin
     insert into stats(stdv) select stddev(age) from T1;
     insert into stats(ave_age) select avg(age) from T1;
     return 1;
    end;
 $$ language plpgsql;

And here are the results to the function:

 select stats_test();

 test_app_development=# select * from stats;
    ave_age | id | stdv 
   ---------+----+-------
            |  1 | 9.496
    42.167  |  2 |     
   (2 rows)

I have tried variations but with no luck:

  insert into stats(ave_age,stdv) select avg(age), stddev(age) from T1;

Best Answer

I have tried it with this function, it works fine for me.

create or replace function stats_test() returns numeric as $$
  begin
    insert into stats(ave_age, stdv) select avg(age), stddev(age) from "T1";
    return 1;
  end;
$$ language plpgsql
Related Question