Hi all, this may sound trivial, but I'm not able to create a simple function that returns either an array or a record. For instance, the following:
CREATE TYPE t_agg AS ( t_count int, t_sum int );
CREATE OR REPLACE FUNCTION f_compute() RETURNS t_agg AS $BODY$ DECLARE tot t_agg%rowtype; BEGIN SELECT count(id) , sum( f_value ) INTO STRICT tot FROM my_table;
RETURN tot;
END $BODY$ LANGUAGE plpgsql;
provides a tuple of t_agg with a simple count and sum.
1) is it possible to change the return value to int[] (and consequently tot variable) using the SELECT INTO statement?
yes
postgres=# create or replace function fx() returns pt as $$ declare result pt; begin select 1,2 into result; return result; end; $$ language plpgsql;
postgres=# select * from fx(); ┌───┬───┐ │ x │ y │ ╞═══╪═══╡ │ 1 │ 2 │ └───┴───┘ (1 row)
create or replace function fx1() returns pt[] as $$ declare result pt[]; begin select array_agg(row(1,2)) from generate_series(1,5) into result; return result; end; $$ language plpgsql; CREATE FUNCTION postgres=# select * from fx1(); ┌───────────────────────────────────────────┐ │ fx1 │ ╞═══════════════════════════════════════════╡ │ {"(1,2)","(1,2)","(1,2)","(1,2)","(1,2)"} │ └───────────────────────────────────────────┘ (1 row)
2) if I change the return type to record (and consequently the tot variable), how should I name columns so that the parse knows what tuple it is returning still using the SELECT INTO?
When you use RECORD as result type, then you have to use tuple descriptor when you are call function
create or replace function fx2() returns record as $$ declare result pt; begin select 1,2 into result; return result; end; $$ language plpgsql;
